﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using Telerik.WinControls;
using Telerik.WinControls.UI;
using System.Data.SqlClient;
using System.IO;

namespace PlanningDepartment
{
    public partial class FrPlanning : Telerik.WinControls.UI.RadForm
    {
        public FrPlanning()
        {
            InitializeComponent();
             // Everytime timer ticks, timer_Tick will be called
          
           
        }
        private void lnk_Black_LinkClicked(object sender, LinkLabelLinkClickedEventArgs e)
        {
            ChangeThemeName(this, "Office2010Black");
        }

        private void lnk_Blue_LinkClicked(object sender, LinkLabelLinkClickedEventArgs e)
        {
            ChangeThemeName(this, "Office2010Blue");
        }
       
        private SqlConnection Conn;
        private Process prs = new Process();

        private DataSet ds = new DataSet();

        private int i = 0, j = 0, k = 0, count = 0, int_temp = 0;
        private double temp_double = 0,SLNhap_d,SLXuat_d,SoKhoi_d,SoLuong_d;
        private DateTime time;
        private string string_temp = null, child = " - ",MaSP1,MaCT1,Datebegin,Dateend,GhiChu1,MaKH1,TenKH1,KhuVuc1,ID,TenCT1,TenSP1;
        #region //Class Public
        private void ChangeThemeName(Control control, string themeName)
        {
            IComponentTreeHandler radControl = control as IComponentTreeHandler;
            if (radControl != null) { radControl.ThemeName = themeName; }
            foreach (Control child in control.Controls)
            { ChangeThemeName(child, themeName); }
        }
        private void cb1(RadDropDownList cb, string sql,SqlConnection Conn, int column1, int column2)
        {
            //Data dta = new Data();
            //SqlConnection Conn = dta.Connect();
            //Conn = prs.Connect_DBScansia();
            try
            {
                
                SqlCommand cmd = new SqlCommand(sql, Conn);
                SqlDataReader rd = cmd.ExecuteReader();
                while (rd.Read())
                {
                    cb.Items.Add(rd[column1].ToString() + " - " + rd[column2].ToString());
                }
                
                cmd.Dispose();
                rd.Close();
            }
            catch (Exception ex)
            { MessageBox.Show("Lỗi load Combobox " + ex.Message); }

        }
        private void Load_()
        {
            SqlCommand cmd = new SqlCommand();
            Conn = prs.Connect_DBScansia();
            Conn.Open();
            try
            {
               
                cmd.CommandText = "select MaSP,TenSP from dbo.Load_cbSP";
                cmd.Connection = Conn;
                SqlDataReader rd = cmd.ExecuteReader();
                while (rd.Read())
                {
                    cb_MaSP_KV1Search.Items.Add(rd[1].ToString() + " - " + rd[0].ToString());
                    cb_SanPham_MuaGo.Items.Add(rd[1].ToString() + " - " + rd[0].ToString());
                    cb_MaSP_KV2Search.Items.Add(rd[1].ToString() + " - " + rd[0].ToString());
                    //cb.Items.Add(rd[column1].ToString() + " - " + rd[column2].ToString());
                    cb_MaSP_KV5Search.Items.Add(rd[1].ToString() + " - " + rd[0].ToString());
                    cb_MaSP_KV1TK.Items.Add(rd[1].ToString() + " - " + rd[0].ToString());
                    cb_MaSP_KV5TK.Items.Add(rd[1].ToString() + " - " + rd[0].ToString());
                    cb_MaSP_LKHKH.Items.Add(rd[1].ToString() + " - " + rd[0].ToString());
                    cb_MaSPTinhKH.Items.Add(rd[1].ToString() + " - " + rd[0].ToString());
                    cb_MaSP_KV3.Items.Add(rd[1].ToString() + " - " + rd[0].ToString());
                    cb_MaSP_ReportKV3.Items.Add(rd[1].ToString() + " - " + rd[0].ToString());
                    cb_MaSP_XX.Items.Add(rd[1].ToString() + " - " + rd[0].ToString());
                }
                rd.Close();
                cmd.CommandText = "select MaLyDo,LyDo from dbo.LyDo where KV='KhoTP'";
                rd = cmd.ExecuteReader();
                while (rd.Read())
                {
                    cb_LyDo_KV5Search.Items.Add(rd[1].ToString() + " - " + rd[0].ToString());
                }

                //cmd.Dispose();
                rd.Dispose();
                DataSet ds = new DataSet();

                
               //Ban Dau string_temp = @"select dbo.Load_CTSP_Mua.MaSP,dbo.Load_CTSP_Mua.TenChiTiet+' - '+dbo.Load_CTSP_Mua.MaChiTiet,dbo.Load_CTSP_Mua.ChieuDaySC,dbo.Load_CTSP_Mua.ChieuRongSC,dbo.Load_CTSP_Mua.ChieuDaiSC from dbo.Load_CTSP_Mua inner join dbo.ChiTiet_KhuVuc on ChiTiet_KhuVuc.MaCT=dbo.Load_CTSP_Mua.MaChiTiet and (ChiTiet_KhuVuc.KhuVuc='KV2XP' or ChiTiet_KhuVuc.KhuVuc='KV3')";
                string_temp = @"select dbo.Load_CTSP_Mua.MaSP,dbo.Load_CTSP_Mua.TenChiTiet+' - '+dbo.Load_CTSP_Mua.MaChiTiet,dbo.Load_CTSP_Mua.ChieuDaySC,dbo.Load_CTSP_Mua.ChieuRongSC,dbo.Load_CTSP_Mua.ChieuDaiSC from dbo.Load_CTSP_Mua";
                ds = prs.dts(string_temp, Conn, ds);
                for (i = 0; i < ds.Tables[0].Rows.Count; i++)
                {
                    dataGridView_LoadSP.Rows.Add(ds.Tables[0].Rows[i][0].ToString(), ds.Tables[0].Rows[i][1].ToString(), ds.Tables[0].Rows[i]["ChieuDaySC"].ToString(), ds.Tables[0].Rows[i]["ChieuRongSC"].ToString(), ds.Tables[0].Rows[i]["ChieuDaiSC"].ToString());
                }
                
                //ChangeThemeName(this, "Office2010Black");
                Control control = this;
                IComponentTreeHandler radControl = control as IComponentTreeHandler;
                if (radControl != null) { radControl.ThemeName = "Office2010Blue"; }
                cmd.CommandText = "select convert(varchar(10),getdate(),103)";
                string_temp = cmd.ExecuteScalar().ToString();
                txt_Datebegin__KV1Search.Text = string_temp;
                txt_Dateend_KV1Search.Text = string_temp;
                txt_Datebegin_KV2Search.Text = string_temp;
                txt_Dateend_KV2Search.Text = string_temp;
                txt_Datebegin_KV5Search.Text = string_temp;
                txt_Dateend_KV5Search.Text = string_temp;
                dtp_DateBegin_KV1.Text = string_temp;
                dtp_DateEnd_KV1.Text = string_temp;
                dtp_Datebegin_KV5TK.Text = string_temp;
                dtp_Dateend_KV5TK.Text = string_temp;
                txt_DateEnd_KV3.Text = string_temp;
                txt_Datebegin_KV3.Text = string_temp;
                dtp_DateBegin_KV3TK.Text = string_temp;
                dtp_DateEnd_KV3TK_.Text = string_temp;
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
            finally
            {
                Conn.Close();
                ds.Clear();
                cmd.Dispose();
            }
        }
        private void FrMain_Load(object sender, EventArgs e)
        {
            Load_();
        }
        private string Date_Split(string Date)
        {
            string Ngay_split;
            string[] temp = Date.Split('/');
            Ngay_split = temp[1] + "/" + temp[0] + "/" + temp[2];
            return Ngay_split;
        }
        private void RowFormatting(RadGridView gr,object sender, RowFormattingEventArgs e)
        {
           
                    //radGridView2.Rows.
               
           
            
        }
        private void FrMain_KeyDown(object sender, KeyEventArgs ex)
        {
            if (ex.KeyData == Keys.Enter)
            {
                SendKeys.Send("{TAB}");
            }
            if (ex.KeyData == Keys.F3)
            {
                if (Page_Main.SelectedPage == Page_KhuVuc2)
                    TD_LXP();
            }
            if (ex.KeyData == Keys.F5)
            {
                if (Page_Main.SelectedPage == Page_KhuVuc3)
                {
                    TD_KeHoach();
                }
            }
        }
        private void lb_Area_Planning_TextChanged(object sender, EventArgs e)
        {
            if (lb_Area_Planning.Text == "KV5")
            {
                //Page_TTKH.Dispose();
                //Page_NhaCungCap.Dispose();
                Page_ThongTin.Dispose();
                Page_LapKeHoach.Dispose();
                Page_KhuVuc1.Dispose();
                Page_KhuVuc2.Dispose();
                Page_KhuVuc3.Dispose();
                Page_KhuVuc4.Dispose();

            }

        }
        #endregion
        #region  // Dang Nhap
        public void Users(string Users)
        {
            Process dta = new Process();
            string sql = @"SELECT
                      [UsersName]
                      ,[Password]
                      ,[GroupUser]
                      ,[Area]
                      ,[Images]
                      ,[MaNV]
                      ,[ChucVu]
                      ,[HovaTen]
                      ,convert(varchar(20),[DateAccess],113) as DateAccess
      
                  FROM [dbo].[Users] where UsersName='"+Users+"'";
            //if (dta.check_input(sql, txt_UserName.Text, 0) && dta.check_input(sql, txt_Password.Text, 1))
            SqlConnection Conn = dta.Connect();
            SqlCommand cmd = new SqlCommand();
            try
            {
                Conn.Open();
                cmd.Connection = Conn;
                cmd.CommandText = sql;
                SqlDataReader rd = cmd.ExecuteReader();
               
                while (rd.Read())
                {
 
                        Enable_form(rd["HovaTen"].ToString(), rd["Area"].ToString());
                        lb_Useraccount.Text = rd["UsersName"].ToString();
                        lb_HovaTen.Text = rd["HovaTen"].ToString();
                        lb_ChucVu.Text = rd["ChucVu"].ToString();
                        lb_MaNV.Text = rd["MaNV"].ToString();
                        lb_NgayTruyCap.Text = rd["DateAccess"].ToString();
                        
                        if (DBNull.Value.Equals(rd["Images"]))
                        {
                            //MessageBox.Show("aaa");
                            pictureBox1.Image = null;
                        }
                        else
                        {
                            dta.get_image(pictureBox1, (byte[])(rd["Images"]));
                        }
                        //MessageBox.Show("asasdas");
                        lb_Area_Planning.Text = rd["Area"].ToString();
                         
                }
                rd.Close(); 
                   
               
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
            finally
            {
                Conn.Close();
                cmd.Dispose();
            }
        }
        byte[] ReadFile(string sPath)
        {
            //Initialize byte array with a null value initially.
            byte[] data = null;

            //Use FileInfo object to get file size.
            FileInfo fInfo = new FileInfo(sPath);
            long numBytes = fInfo.Length;

            //Open FileStream to read file
            FileStream fStream = new FileStream(sPath, FileMode.Open, FileAccess.Read);

            //Use BinaryReader to read file stream into byte array.
            BinaryReader br = new BinaryReader(fStream);

            //When you use BinaryReader, you need to supply number of bytes to read from file.
            //In this case we want to read entire file. So supplying total number of bytes.
            data = br.ReadBytes((int)numBytes);
            return data;
        }

        /*private void bt_DangNhap_Click(object sender, EventArgs e)
        {
            Process dta = new Process();
            string sql = @"SELECT
                      [UsersName]
                      ,[Password]
                      ,[GroupUser]
                      ,[Area]
                      ,[Images]
                      ,[MaNV]
                      ,[ChucVu]
                      ,[HovaTen]
                      ,convert(varchar(20),[DateAccess],113) as DateAccess
      
                  FROM [Planning].[dbo].[Users]";
            //if (dta.check_input(sql, txt_UserName.Text, 0) && dta.check_input(sql, txt_Password.Text, 1))
            SqlConnection Conn = dta.Connect();
            SqlCommand cmd = new SqlCommand();
            try
            {
                Conn.Open();
                cmd.Connection = Conn;
                cmd.CommandText = sql;
                SqlDataReader rd = cmd.ExecuteReader();
                int i = 0;
                while (rd.Read())
                {
                    if (txt_UserName.Text == rd[0].ToString() && txt_Password.Text == rd[1].ToString())
                    {
                        MessageBox.Show("Đăng nhập thành công");
                        Enable_form(rd["HovaTen"].ToString(),rd["Area"].ToString());
                        lb_Useraccount.Text = rd["UsersName"].ToString();
                        lb_HovaTen.Text = rd["HovaTen"].ToString();
                        lb_ChucVu.Text = rd["ChucVu"].ToString();
                        lb_MaNV.Text = rd["MaNV"].ToString();
                        lb_NgayTruyCap.Text = rd["DateAccess"].ToString();
                        txt_UserName.Visible = false;
                        txt_Password.Visible = false;
                        bt_DangNhap.Visible = false;
                        if (DBNull.Value.Equals(rd["Images"]))
                        {
                            //MessageBox.Show("aaa");
                            pictureBox1.Image = null;
                        }
                        else
                        {
                            dta.get_image(pictureBox1,(byte[])(rd["Images"]));
                        }
                        //MessageBox.Show("asasdas");
                        rd.Close();
                       
                        //MessageBox.Show(date);
                        string update = @"UPDATE [dbo].[Users] SET DateAccess=getdate() where UsersName='" + txt_UserName.Text + "'";
                       // MessageBox.Show(update);
                        cmd.CommandText = update;
                        cmd.ExecuteNonQuery();
                        i = 0;
                        break;
                    }
                    else
                        i++;
                }
                if (i > 0)
                {
                    MessageBox.Show("Thông tin không chính xác");
                    txt_UserName.Focus();
                }
                else
                {
                    txt_UserName.Text = "";
                    txt_Password.Text = "";
                    //this.Close();
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
            finally
            {
                Conn.Close();
                cmd.Dispose();
            }
        }*/

        private void bt_Save_User_Click(object sender, EventArgs e)
        {
            Conn = prs.Connect();
            Conn.Open();
            SqlCommand cmd = new SqlCommand();
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Connection = Conn;

            try
            {
                byte[] imageData = null;
                //Read Image Bytes into a byte array
                if (lbImage.Text != "Image")
                    imageData = ReadFile(lbImage.Text);



                //Initialize SQL Server Connection
                //SqlConnection CN = new SqlConnection(txtConnectionString.Text);

                
                if (txt_Users.Text == "" || txt_pass.Text == "" || txt_GroupUser.Text == "" || txt_KhuVuc.Text == "")
                    MessageBox.Show("Phải nhập đầy đủ các thông tin có dấu (*)");
                else
                {
                    cmd.Parameters.AddWithValue("@UsersName", txt_Users.Text.Trim());
                    cmd.Parameters.AddWithValue("@Password", txt_pass.Text.Trim());
                    cmd.Parameters.AddWithValue("@GroupUser", txt_GroupUser.Text.Trim());
                    cmd.Parameters.AddWithValue("@Area", txt_KhuVuc.Text.Trim());
                    cmd.Parameters.AddWithValue("@MaNV", txt_MaNV.Text.Trim());
                    cmd.Parameters.AddWithValue("@ChucVu", txt_ChucVu.Text.Trim());
                    cmd.Parameters.AddWithValue("@HovaTen", txt_Ho.Text.Trim());
                    if (imageData != null)
                    {
                        if (imageData.Length > 100000)
                            MessageBox.Show("Bạn nên giảm dung lượng ảnh lại trước khi lưu");
                        else
                        {
                            count = 1;
                            cmd.CommandText = "Ins_Users";
                            cmd.Parameters.AddWithValue("@Images", (object)imageData);
                        }
                    }
                    else
                    {
                        count = 1;
                        cmd.CommandText = "Ins_Users1";
                    }
                    if (count == 1)
                    { cmd.ExecuteNonQuery(); }
                    cmd.Parameters.Clear();
                    //Page_User.Text = "aaa";
                    lbImage.Text = "Image";
                    txt_pass.Text = "";
                    txt_Users.Text = "";
                    txt_GroupUser.Text = "";
                    txt_MaNV.Text = "";
                    txt_Ho.Text = "";
                    txt_ChucVu.Text = "";
                    pictureBox2.Image = null;
                    //reset_TTNV();
                    MessageBox.Show("Dữ liệu được lưu thành công");

                }
            }
            catch (Exception ex)
            {
                string s = "insert duplicate key";
                string FK = "FOREIGN KEY constraint";
                string s1 = ex.Message.ToString();
                if (s1.Contains(s))
                { MessageBox.Show("Insert không thành công \n" + "Mã đã tồn tại "); }
                else if (s1.Contains(FK))
                { MessageBox.Show("Nhập sai thông tin trong Combobox"); }
                DialogResult Err;
                Err = MessageBox.Show("Đã xảy ra lỗi, dữ liệu lưu không thành công " + ex.ToString(), "THÔNG BÁO", MessageBoxButtons.OK, MessageBoxIcon.Error);
                //MessageBox.Show(ex.ToString());
            }
            finally
            {
                Conn.Close();
                cmd.Dispose();
            }
        }
        private string Openfile_Image()
        {
            string path = null;
            OpenFileDialog dlg = new OpenFileDialog();
            DialogResult dlgRes = dlg.ShowDialog();
            if (dlgRes != DialogResult.Cancel)
            {
                //Set image in picture box
                pictureBox2.ImageLocation = dlg.FileName;

                //Provide file path in txtImagePath text box.
                path = dlg.FileName;
                return path;
            }
            return null;
        }
        private void pictureBox2_DoubleClick(object sender, EventArgs e)
        {
            lbImage.Text = Openfile_Image();
        }
        private void Enable_form(string Username,string groupuser)
        {
            Page_Main.Visible = true;
            Page_User.Text = Username;
            if (groupuser == "administrator")
                GroupBox1.Visible = true;
            else
                GroupBox1.Visible = false;
        }
        #endregion
        #region //Tinh Ke hoach mua go
        private string TinhKH(string MaSP_MuaGo,double SLSP)
        {
            /*DataSet ds = new DataSet();
            Conn = prs.Connect_DBScansia();
            Conn.Open();
           
                
                if (cb_SanPham_MuaGo.Text != "")
                {
                    MaSP_MuaGo = prs.substring_right(cb_SanPham_MuaGo.Text, child);
                }
                if(txt_SLKH_MuaGo.Text!="")
                     temp_double= double.Parse(txt_SLKH_MuaGo.Text);
                if (cb_Loai.Text == ""||MaSP_MuaGo==""||txt_SLKH_MuaGo.Text=="")
                    MessageBox.Show("Bạn cần nhập đầy đủ thông tin trước khi tính");*/
            string_temp = "";
                if (cb_Loai.Text == "Chi Tiet")
                {
                    string_temp = @"
                            select KeHoach.TenSP,ChiTiet_KhuVuc.MaSP,KeHoach.TenChiTiet,ChiTiet_KhuVuc.MaCT,KeHoach.SoLuongCT,KeHoach.ChieuDaySC,KeHoach.ChieuRongSC,KeHoach.ChieuDaiSC,Table1.SLLP/Table1.SLCT_QuyDoi*ChiTiet_KhuVuc.SLCT_QuyDoi as SLLP,
                            Table1.SLHCLP*ChiTiet_KhuVuc.SLCT_QuyDoi/Table1.SLCT_QuyDoi as SLHCLP,Table1.SLCTNKTP*ChiTiet_KhuVuc.SLCT_QuyDoi/Table1.SLCT_QuyDoi as SLNKTP,Table1.SLHCKTP*ChiTiet_KhuVuc.SLCT_QuyDoi/Table1.SLCT_QuyDoi as SLHCKTP, 
                            Table1.SLLoai*ChiTiet_KhuVuc.SLCT_QuyDoi/Table1.SLCT_QuyDoi as SLLoai,Table1.TonKho*ChiTiet_KhuVuc.SLCT_QuyDoi/Table1.SLCT_QuyDoi as TonKTG,KeHoach.SLKH,
                            (KeHoach.SLKH-(Table1.SLLP+Table1.SLHCLP-(Table1.SLCTNKTP+Table1.SLHCKTP)-SLLoai+Table1.TonKho))*ChiTiet_KhuVuc.SLCT_QuyDoi/Table1.SLCT_QuyDoi as SLThieu
                            from
                            (select KTG.*,KTP.SLCTNKTP,SLHCKTP,KTP.SLCT_QuyDoi,isnull(HangLoai.SLLoai,0) as SLLoai from
                            (select qry1.MaCT,qry1.SLLP,isnull(HieuChinhSL.SoLuong,0) as SLHCLP,KTG_TK.TonKho from 
                            dbo.KTG_TK left join (select MaCT,sum(SLXuat) as SLLP from dbo.KV1_KTG where Ngay>='08/01/2013 00:00' group by MaCT) as qry1 on KTG_TK.MaCT=qry1.MaCT
                            left join dbo.HieuChinhSL on (dbo.HieuChinhSL.MaCT=KTG_TK.MaCT and HieuChinhSL.KhuVuc='KV1X') ) as KTG
                            left join
                            (select qry2.MaSP,ChiTietLoai.MaCT,qry2.SLNhapKTP*SLCT_QuyDoi as SLCTNKTP,ChiTietLoai.SLCT_QuyDoi,ISNULL(ChiTietLoai.SoLuong,0) as SLHCKTP
                            from (select dbo.KhoTP_NK.MaSP,sum(KhoTP_NK.SLNhap) as SLNhapKTP from KhoTP_NK where KhoTP_NK.LyDo='TPNM' and Ngay>='08/01/2013 00:00' group by MaSP) as qry2 
                            inner join (select ChiTiet_KhuVuc.SLCT_QuyDoi,ChiTiet_KhuVuc.MaSP,ChiTiet_KhuVuc.MaCT,HieuChinhSL.SoLuong*ChiTiet_KhuVuc.SLCT_QuyDoi as SoLuong from (select MaSP,ChiTiet_KhuVuc.MaCT,SLCT_QuyDoi from dbo.ChiTiet_KhuVuc where KhuVuc='KV1X') as ChiTiet_KhuVuc left join HieuChinhSL on ChiTiet_KhuVuc.MaSP=HieuChinhSL.MaSP and HieuChinhSL.KhuVuc='KhoTP') as ChiTietLoai on ChiTietLoai.MaSP=qry2.MaSP) as KTP on KTG.MaCT=KTP.MaCT
                            left join (select MaCT, sum(SLLoai) as SLLoai from dbo.TK_HangLoai where Ngay>='08/01/2013 00:00' group by MaCT) as HangLoai on HangLoai.MaCT=KTG.MaCT) as Table1
                            inner join ChiTiet_KhuVuc on (ChiTiet_KhuVuc.ID_link=Table1.MaCT and ChiTiet_KhuVuc.KhuVuc='KV1SB' and MaSP='" + MaSP_MuaGo + "')";
                    string_temp += "   inner join (select TenSP,MaSP,MaChiTiet,SoLuongCT*" + SLSP.ToString() + " as SLKH,TenChiTiet,ChieuDaySC,ChieuRongSC,ChieuDaiSC,SoLuongCT from dbo.Load_CTSP_Mua where MaSP='" + MaSP_MuaGo + "') as KeHoach on KeHoach.MaChiTiet=ChiTiet_KhuVuc.MaCT";
                }
                if (cb_Loai.Text == "Quy Cach")
                {
                    string_temp = @"
                            select '' as TenSP,'' as MaSP,'' as TenChiTiet,'' as MaCT,'' as SoLuongCT,KeHoach.ChieuDaySC,KeHoach.ChieuRongSC,KeHoach.ChieuDaiSC,sum(Table1.SLLP/Table1.SLCT_QuyDoi*ChiTiet_KhuVuc.SLCT_QuyDoi) as SLLP,
                            sum(Table1.SLHCLP*ChiTiet_KhuVuc.SLCT_QuyDoi/Table1.SLCT_QuyDoi) as SLHCLP,sum(Table1.SLCTNKTP*ChiTiet_KhuVuc.SLCT_QuyDoi/Table1.SLCT_QuyDoi) as SLNKTP,sum(Table1.SLHCKTP*ChiTiet_KhuVuc.SLCT_QuyDoi/Table1.SLCT_QuyDoi) as SLHCKTP, 
                            sum(Table1.SLLoai*ChiTiet_KhuVuc.SLCT_QuyDoi/Table1.SLCT_QuyDoi) as SLLoai,sum(Table1.TonKho*ChiTiet_KhuVuc.SLCT_QuyDoi/Table1.SLCT_QuyDoi) as TonKTG,sum(KeHoach.SLKH) as SLKH,
                            SUM(KeHoach.SLKH-(Table1.SLLP+Table1.SLHCLP-(Table1.SLCTNKTP+Table1.SLHCKTP)-SLLoai+Table1.TonKho)*ChiTiet_KhuVuc.SLCT_QuyDoi/Table1.SLCT_QuyDoi) as SLThieu
                            from
                            (select KTG.*,KTP.SLCTNKTP,SLHCKTP,KTP.SLCT_QuyDoi,isnull(HangLoai.SLLoai,0) as SLLoai from
                            (select qry1.MaCT,qry1.SLLP,isnull(HieuChinhSL.SoLuong,0) as SLHCLP,KTG_TK.TonKho from 
                            dbo.KTG_TK left join (select MaCT,sum(SLXuat) as SLLP from dbo.KV1_KTG where Ngay>='08/01/2013 00:00' group by MaCT) as qry1 on KTG_TK.MaCT=qry1.MaCT
                            left join dbo.HieuChinhSL on (dbo.HieuChinhSL.MaCT=KTG_TK.MaCT and HieuChinhSL.KhuVuc='KV1X') ) as KTG
                            left join
                            (select qry2.MaSP,ChiTietLoai.MaCT,qry2.SLNhapKTP*SLCT_QuyDoi as SLCTNKTP,ChiTietLoai.SLCT_QuyDoi,ISNULL(ChiTietLoai.SoLuong,0) as SLHCKTP
                            from (select dbo.KhoTP_NK.MaSP,sum(KhoTP_NK.SLNhap) as SLNhapKTP from KhoTP_NK where KhoTP_NK.LyDo='TPNM' and Ngay>='08/01/2013 00:00'  group by MaSP) as qry2 
                            inner join (select ChiTiet_KhuVuc.SLCT_QuyDoi,ChiTiet_KhuVuc.MaSP,ChiTiet_KhuVuc.MaCT,HieuChinhSL.SoLuong*ChiTiet_KhuVuc.SLCT_QuyDoi as SoLuong from (select MaSP,ChiTiet_KhuVuc.MaCT,SLCT_QuyDoi from dbo.ChiTiet_KhuVuc where KhuVuc='KV1X') as ChiTiet_KhuVuc left join HieuChinhSL on ChiTiet_KhuVuc.MaSP=HieuChinhSL.MaSP and HieuChinhSL.KhuVuc='KhoTP') as ChiTietLoai on ChiTietLoai.MaSP=qry2.MaSP) as KTP on KTG.MaCT=KTP.MaCT
                            left join (select MaCT, sum(SLLoai) as SLLoai from dbo.TK_HangLoai where Ngay>='08/01/2013 00:00' group by MaCT) as HangLoai on HangLoai.MaCT=KTG.MaCT) as Table1
                            inner join ChiTiet_KhuVuc on (ChiTiet_KhuVuc.ID_link=Table1.MaCT and ChiTiet_KhuVuc.KhuVuc='KV1SB' and MaSP='" + MaSP_MuaGo + "')";
                    string_temp += @"    inner join (select TenSP,MaSP,MaChiTiet,SoLuongCT*" + SLSP.ToString() + " as SLKH,TenChiTiet,ChieuDaySC,ChieuRongSC,ChieuDaiSC from dbo.Load_CTSP_Mua where MaSP='" + MaSP_MuaGo + "') as KeHoach on KeHoach.MaChiTiet=ChiTiet_KhuVuc.MaCT group by KeHoach.ChieuDaySC,KeHoach.ChieuRongSC,KeHoach.ChieuDaiSC";
                }
                return string_temp;
                
            
           
        }
        private void bt_reset_MuaGo_Click(object sender, EventArgs e)
        {
            if (radGridView1.Rows.Count > 0)
                radGridView1.Rows.Clear();
            bt_Tinh.Enabled = true;
        }
        private void KHMuaGo()
        {
            //DataSet ds = new DataSet();
            SqlCommand cmd = new SqlCommand();
            SqlDataReader rd = null;
            Conn = prs.Connect_DBScansia();
            Conn.Open();
            cmd.Connection = Conn;
            try
            {
                if (radGridView8.Rows.Count > 0)
                {
                    for (i = 0; i < radGridView8.Rows.Count; i++)
                    {
                        cmd.CommandText=TinhKH(radGridView8.Rows[i].Cells["MaSP_LKH"].Value.ToString(),double.Parse(radGridView8.Rows[i].Cells["SLSP_LKH"].Value.ToString()));
                        rd = cmd.ExecuteReader();
                        while (rd.Read())
                        {
                            radGridView1.Rows.Add(rd[0].ToString(), rd[1].ToString(), rd[2].ToString(), rd[3].ToString(), rd[4].ToString(), rd[5].ToString(), rd[6].ToString(), rd[7].ToString(), rd[8].ToString(), rd[9].ToString(), rd[10].ToString(), rd[11].ToString(), rd[12].ToString(), rd[13].ToString(), rd[14].ToString(), rd[15].ToString());
                            
                        }
                        rd.Close();

                    }
                    bt_Tinh.Enabled = false;
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show("Đã xảy ra lỗi, Kiểm tra lại thông tin đầu vào",ex.Message);
                radGridView1.Rows.Clear();

            }
            finally
            {
                Conn.Close();
                cmd.Dispose();
                rd.Dispose();
            }

        }
        private void bt_addgrid_LKH_Click(object sender, EventArgs e)
        {
            if (cb_SanPham_MuaGo.Text !=""&&txt_SLKH_MuaGo.Text!="")
            {
                MaSP1 = prs.substring_right(cb_SanPham_MuaGo.Text, child);
                TenSP1=prs.substring_left(cb_SanPham_MuaGo.Text,child);
                if (MaSP1 == "")
                    MessageBox.Show("Nhập sai thông tin");
                else
                {
                    int_temp = 0;
                    string_temp = txt_SLKH_MuaGo.Text;
                    if (radGridView8.Rows.Count > 0)
                    {
                        for (i = 0; i < radGridView8.Rows.Count; i++)
                        {
                            if (MaSP1 == radGridView8.Rows[i].Cells["MaSP_LKH"].Value.ToString())
                            {
                                MessageBox.Show("Sản phẩm này đã có trong danh sách phía dưới");
                                int_temp = 1;
                                break;
                            }
                          
                        }
                    }
                    if (int_temp<1)
                    {
                        radGridView8.Rows.Add(MaSP1, TenSP1, string_temp);
                        cb_SanPham_MuaGo.Text = "";
                        txt_SLKH_MuaGo.Text = "";
                        cb_SanPham_MuaGo.Focus();
                    }
                }

            }
        }

        private void radGridView8_DoubleClick(object sender, EventArgs e)
        {
            if (radGridView8.Rows.Count > 0)
            {
                try
                {
                    //MessageBox.Show(radGridView1.Rows.Count.ToString());
                    int r = radGridView8.CurrentCell.RowIndex;


                    radGridView8.Rows.RemoveAt(r);


                    //radGridView1.CurrentCell = radGridView1.Rows[j].Cells["MaCTinput"];

                }
                catch (Exception ex)
                { MessageBox.Show(ex.Message); }
            }
        }

        private void bt_Tinh_Click(object sender, EventArgs e)
        {
            try
            {
                /*DataTable ds = new DataTable();
                //ds = TinhKH();
               
                if (ds.Rows.Count > 0)
                    radGridView1.DataSource = ds;
                else
                    MessageBox.Show("Không có dữ liệu");*/
                KHMuaGo();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }
        private void exportexcel_MuaGo()
        {
            try
            {
                
                //DataTable dt = (DataTable)radGridView1.DataSource;
                if (radGridView1.Rows.Count > 0)
                {
                    prs.ExportExcel_grid(radGridView1, "C:\\Huynh\\KeHoachMuaGo.xlsx", 6, 1);
                }
                else
                    MessageBox.Show("Không có dữ liệu");
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
            
        }
        private void txt_SLKH_MuaGo_Leave(object sender, EventArgs e)
        {
            prs.checktext_number(txt_SLKH_MuaGo);
        }
        private void bt_excel_MuaGo_Click(object sender, EventArgs e)
        {
            exportexcel_MuaGo();
        }
        #endregion
        #region //Lap Ke Hoach
        private void save_LKH()
        {
            Conn = prs.Connect_DBScansia();
            SqlCommand cmd = new SqlCommand();
            Conn.Open();
            cmd.Connection = Conn;
            MaKH1 = txt_MaKH.Text.Trim();
            if (MaKH1.Length < 5)
            {
                if (MaKH1.Length == 1)
                    MaKH1 = "0000" + MaKH1;
                if (MaKH1.Length == 2)
                    MaKH1 = "000" + MaKH1;
                if (MaKH1.Length == 3)
                    MaKH1 = "00" + MaKH1;
                if (MaKH1.Length == 4)
                    MaKH1 = "0" + MaKH1;
            }
            TenKH1 = txt_TenKH.Text;
            KhuVuc1 = cb_KhuVuc_LKHKH.Text.Trim();
            SoLuong_d = double.Parse(txt_SoLuong_LKHKH.Text);
            if (cb_MaSP_LKHKH.Text != "")
                MaSP1 = prs.substring_right(cb_MaSP_LKHKH.Text, child);
            else
                MaSP1 = "";
            GhiChu1 = txt_GhiChu_LKHKH.Text;
            cmd.CommandText = "select datepart(yyyy,getdate())";

            ID = MaKH1 + KhuVuc1 + cmd.ExecuteScalar().ToString();
            //GhiChu1 = txt_GhiChu.Text;
            if (MaKH1.Length == 5)
            {
                if (KhuVuc1 == "" || MaSP1 == "")
                    MessageBox.Show("Nhập sai thông tin trong ComboBox");
                else
                {
                    string insert1 = @"INSERT INTO [dbo].[KeHoachSP]([ID],[MaKeHoach],[TenKeHoach],[MaSP],[SoLuongSP],[KhuVuc],[GhiChu])
                        VALUES(N'" + ID + "',N'" + MaKH1 + "',N'" + TenKH1 + "',N'" + MaSP1 + "'," + SoLuong_d + ",N'" + KhuVuc1 + "',N'" + GhiChu1 + "')";

                    try
                    {
                        cmd.CommandText = insert1;
                        cmd.ExecuteNonQuery();
                        if (rd_SP_LKHKH.IsChecked== true)
                        {
                            cmd.CommandText = @"insert into dbo.KH_TheoDoi(ID,MaCT,ID_KeHoach,SLKH,SLTheoDoi,Ngay)
                                    SELECT     dbo.KeHoachSP.ID+dbo.ChiTiet_KhuVuc.MaCT as ID,dbo.ChiTiet_KhuVuc.MaCT as MaCT,dbo.KeHoachSP.ID as ID_KeHoach, dbo.ChiTiet_KhuVuc.SLCT_QuyDoi * dbo.KeHoachSP.SoLuongSP AS SLKH,dbo.ChiTiet_KhuVuc.SLCT_QuyDoi * dbo.KeHoachSP.SoLuongSP as SLTheoDoi,getdate() as Ngay
                                    FROM         dbo.ChiTiet_KhuVuc INNER JOIN
                                    dbo.KeHoachSP ON dbo.ChiTiet_KhuVuc.MaSP = dbo.KeHoachSP.MaSP
                                    WHERE     (dbo.ChiTiet_KhuVuc.KhuVuc = '" + KhuVuc1 + "' and dbo.KeHoachSP.ID='" + ID + "')";
                        }
                        if (rd_ChiTiet_LKHKH.IsChecked == true)
                        {
                            if (cb_MaCT_LKHKH.Text != "")
                                TenCT1 = prs.substring_right(cb_MaCT_LKHKH.Text, child);
                            if (TenCT1 != "")
                            {
                                cmd.CommandText = @"insert into dbo.KH_TheoDoi(ID,MaCT,ID_KeHoach,SLKH,SLTheoDoi,Ngay)
                                    SELECT     dbo.KeHoachSP.ID+dbo.ChiTiet_KhuVuc.MaCT as ID,dbo.ChiTiet_KhuVuc.MaCT as MaCT,dbo.KeHoachSP.ID as ID_KeHoach, dbo.ChiTiet_KhuVuc.SLCT_QuyDoi * dbo.KeHoachSP.SoLuongSP AS SLKH,dbo.ChiTiet_KhuVuc.SLCT_QuyDoi * dbo.KeHoachSP.SoLuongSP as SLTheoDoi,getdate() as Ngay
                                    FROM         dbo.ChiTiet_KhuVuc INNER JOIN
                                    dbo.KeHoachSP ON dbo.ChiTiet_KhuVuc.MaSP = dbo.KeHoachSP.MaSP
                                    WHERE     (dbo.ChiTiet_KhuVuc.KhuVuc = '" + KhuVuc1 + "' and dbo.KeHoachSP.ID='" + ID + "' and dbo.ChiTiet_KhuVuc.MaCT='" + TenCT1 + "')";
                            }
                            else
                                MessageBox.Show("Nhập sai tên chi tiết");
                        }
                        //MessageBox.Show(cmd.CommandText.ToString());
                        cmd.ExecuteNonQuery();
                        MessageBox.Show("Dữ liệu được lưu thành công");
                        //prs.reset_txt(groupPanel1);
                        txt_MaKH.Text = "";
                        txt_SoLuong_LKHKH.Text = "";
                        cb_MaSP_LKHKH.Text = "";

                    }
                    catch (Exception ex)
                    {
                        string s = "insert duplicate key";
                        //string FK = "FOREIGN KEY constraint";
                        string s1 = ex.Message.ToString();
                        if (s1.Contains(s))
                        { MessageBox.Show("Insert không thành công \n" + "Mã đã tồn tại "); }
                        else
                        {
                            MessageBox.Show(ex.Message);
                            MessageBox.Show("Đã xảy ra lỗi khi thêm dữ liệu, vui lòng kiểm tra lại");
                            cmd.CommandText = @"delete from dbo.KeHoachSP where ID='" + ID + "'";
                            cmd.ExecuteNonQuery();
                        }
                        MessageBox.Show(ex.Message);
                    }
                    finally
                    {
                        Conn.Close();
                        cmd.Dispose();
                    }
                }
            }
            else
                MessageBox.Show("Kế Hoạch Không Dài Quá 5 Ký Tự");

        }
        private void cb_MaSP_LKHKH_SelectedIndexChanged(object sender, Telerik.WinControls.UI.Data.PositionChangedEventArgs e)
        {
            try
            {
                Process prs = new Process();
                if (cb_MaSP_LKHKH.Text != "")
                {
                    MaSP1 = prs.substring_right(cb_MaSP_LKHKH.Text, child);
                    if (MaSP1 == "")
                        MessageBox.Show("Nhập sai mã SP");
                }
                if (rd_ChiTiet_LKHKH.IsChecked == true)
                {
                    //FormProcess Frp = new FormProcess();
                    cb_MaCT_LKHKH.Items.Clear();
                    for (i = dataGridView_LoadSP.Rows.Count - 2; i >= 0; i--)
                    {
                        if (MaSP1 == dataGridView_LoadSP.Rows[i].Cells[0].Value.ToString())
                        {

                            cb_MaCT_LKHKH.Items.Add(dataGridView_LoadSP.Rows[i].Cells[1].Value.ToString());
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }

        private void rd_SP_LKHKH_ToggleStateChanged(object sender, StateChangedEventArgs args)
        {
            lb_ChiTiet_LKHKH.Visible = false;
            cb_MaCT_LKHKH.Visible = false;
        }

        private void rd_ChiTiet_LKHKH_ToggleStateChanged(object sender, StateChangedEventArgs args)
        {
            lb_ChiTiet_LKHKH.Visible = true;
            cb_MaCT_LKHKH.Visible = true;
        }

        private void bt_save_LKHKH_Click(object sender, EventArgs e)
        {
            save_LKH();
        }
        private void txt_SoLuong_LKHKH_Leave(object sender, EventArgs e)
        {
            prs.checktext_number(txt_SoLuong_LKHKH);
        }
        private void Display(int i)
        {
            txt_MaKH.Text = dataGridView2.Rows[i].Cells["MaKeHoach"].Value.ToString();
            cb_MaSP_LKHKH.Text = dataGridView2.Rows[i].Cells["TenSP"].Value.ToString() + " - " + dataGridView2.Rows[i].Cells["MaSP"].Value.ToString();
            txt_TenKH.Text = dataGridView2.Rows[i].Cells["TenKeHoach"].Value.ToString();
            txt_SoLuong_LKHKH.Text = dataGridView2.Rows[i].Cells["SoLuongSP"].Value.ToString();
            txt_GhiChu_LKHKH.Text = dataGridView2.Rows[i].Cells["GhiChu"].Value.ToString();
            cb_KhuVuc_LKHKH.Text = dataGridView2.Rows[i].Cells["KhuVuc"].Value.ToString();
            lb_ID_LKHKH.Text = dataGridView2.Rows[i].Cells["ID"].Value.ToString();
        }
        private void bt_Search_LKHKH_Click(object sender, EventArgs e)
        {

            try
            {
                //Process prs = new Process();
                DataSet ds = new DataSet();
                Conn = prs.Connect_DBScansia();
                Conn.Open();
                if (txt_MaKH.Text != "")
                {
                    string_temp = @"SELECT     dbo.KeHoachSP.ID,dbo.KeHoachSP.MaKeHoach, dbo.KeHoachSP.TenKeHoach, dbo.SanPham.MaSP, dbo.SanPham.TenSP, dbo.KeHoachSP.SoLuongSP, 
                      dbo.KeHoachSP.KhuVuc, dbo.KeHoachSP.GhiChu
                        FROM         dbo.KeHoachSP INNER JOIN
                      dbo.SanPham ON dbo.KeHoachSP.MaSP = dbo.SanPham.MaSP where MaKeHoach=N'" + txt_MaKH.Text + "'";
                    ds = prs.dts(string_temp, Conn, ds);

                }
                else
                {
                    if (cb_MaSP_LKHKH.Text != "")
                    {
                        MaSP1 = prs.substring_right(cb_MaSP_LKHKH.Text, child);
                        if (MaSP1 == "")
                            MessageBox.Show("Nhập sai mã SP");
                        else
                        {
                            string_temp = @"SELECT     dbo.KeHoachSP.MaKeHoach, dbo.KeHoachSP.TenKeHoach, dbo.SanPham.MaSP, dbo.SanPham.TenSP, dbo.KeHoachSP.SoLuongSP, 
                        dbo.KeHoachSP.KhuVuc, dbo.KeHoachSP.GhiChu
                        FROM         dbo.KeHoachSP INNER JOIN
                        dbo.SanPham ON dbo.KeHoachSP.MaSP = dbo.SanPham.MaSP where KeHoachSP.MaSP=N'" + MaSP1 + "'";
                            ds = prs.dts(string_temp, Conn, ds);
                        }
                    }
                }
                
                //MessageBox.Show(ds.Tables[0].Rows.Count.ToString());
                if (ds.Tables[0].Rows.Count > 0)
                {
                    dataGridView2.DataSource = ds.Tables[0];
                    int j = dataGridView2.Rows.Count - 2;
                    Display(j);
                    lb_index.Text = j.ToString();
                    bt_delete_LKHKH.Enabled = true;
                    bt_edit_LKHKH.Enabled = true;
                }
                else
                    MessageBox.Show("Không có thông tin. Vui lòng kiểm tra lại thông tin tìm kiếm");
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
            finally
            {
                Conn.Close();
            }

        }
        private void bt_Prev_LKHKH_Click(object sender, EventArgs e)
        {
            try
            {

                if (dataGridView2.Rows.Count > 0)
                {
                    int i;
                    i = int.Parse(lb_index.Text);
                    if (i > 0)
                    {
                        i--;
                        Display(i);
                        lb_index.Text = i.ToString();
                    }
                }
            }
            catch (Exception ex)
            { MessageBox.Show(ex.Message); }
        }

        private void bt_next_LKHKH_Click(object sender, EventArgs e)
        {
            try
            {
                if (dataGridView2.Rows.Count > 0)
                {
                    int i;
                    i = int.Parse(lb_index.Text);
                    if (i < dataGridView2.Rows.Count - 2)
                    {
                        i++;
                        Display(i);
                        lb_index.Text = i.ToString();
                    }
                }
            }
            catch (Exception ex)
            { MessageBox.Show(ex.Message); }
        }
        private void bt_edit_LKHKH_Click(object sender, EventArgs e)
        {
            DataSet ds = new DataSet();
            Conn = prs.Connect_DBScansia();
            SqlCommand cmd = new SqlCommand();

            Conn.Open();
            cmd.Connection = Conn;
            cmd.CommandText="select datepart(YYYY,GETDATE())";
            try
            {

                ID = txt_MaKH.Text + cb_KhuVuc_LKHKH.Text + cmd.ExecuteScalar().ToString();
                string_temp = @"select KH_Theodoi.ID,Load_CTSP_Mua.TenSP,Load_CTSP_Mua.TenChiTiet,KH_Theodoi.SLKH,KH_Theodoi.SLTheoDoi
                                from dbo.KH_Theodoi inner join Load_CTSP_Mua on Load_CTSP_Mua.MaChiTiet=KH_Theodoi.MaCT
                                where KH_Theodoi.ID_KeHoach ='" + ID + "'";

                ds = prs.dts(string_temp, Conn, ds);
                if (ds.Tables[0].Rows.Count > 0)
                {

                    radGridView7.DataSource = ds.Tables[0];
                    //tabControl1.SelectedTab = tabItem2;
                }
                else
                {
                    MessageBox.Show("Không có dữ liệu");
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
            finally
            {
                Conn.Close();
                cmd.Dispose();
            }
        }

        private void bt_delete_LKHKH_Click(object sender, EventArgs e)
        {
            DialogResult d = MessageBox.Show("Bạn có chắc muốn xóa Kế Hoạch " + txt_MaKH.Text + cb_KhuVuc_LKHKH.Text, "Thông Báo", MessageBoxButtons.OKCancel);
            if (d == DialogResult.OK)
            {
                try
                {
                    string datecheck = @"select datediff(dd,Ngay,getdate()) from dbo.KeHoachSP where ID='" + lb_ID_LKHKH.Text + "'";
                    string delete = @"delete from dbo.KeHoachSP where ID='" + lb_ID_LKHKH.Text + "'";
                    //MessageBox.Show(datecheck);

                    Conn = prs.Connect_DBScansia();
                    Conn.Open();
                    SqlCommand cmd = new SqlCommand();
                    cmd.Connection = Conn;
                    cmd.CommandText = datecheck;
                    int date_count = int.Parse(cmd.ExecuteScalar().ToString());
                    if (date_count <= 1)
                    {
                        cmd.CommandText = delete;
                        cmd.ExecuteNonQuery();
                        MessageBox.Show("Dữ liệu được xóa thành công");
                        lb_ID_LKHKH.Text = "0";
                    }
                    else
                    {
                        MessageBox.Show("Quá thời gian cho phép,Không được phép xóa");
                    }
                    Conn.Close();
                    cmd.Dispose();

                    for (int i = 0; i < radGridView7.Rows.Count - 1; i++)
                    {
                        radGridView7.Rows.RemoveAt(0);
                    }
                    //dta.reset_txt(groupPanel1);
                    bt_delete_LKHKH.Enabled = false;
                    bt_edit_LKHKH.Enabled = false;

                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.Message);
                }
            }
        }

        private void bt_HieuChinhKH_Click(object sender, EventArgs e)
        {
            Conn = prs.Connect_DBScansia();
            SqlCommand cmd = new SqlCommand();
            DataSet ds = new DataSet();
            Conn.Open();
            cmd.Connection = Conn;
            string UpdateKH = "";

            try
            {
                for (int i = radGridView7.Rows.Count - 1; i >= 0; i--)
                {
                    UpdateKH = @"UPDATE [dbo].[KH_Theodoi]
                            SET [SLKH] =" + radGridView7.Rows[i].Cells["SLKH"].Value.ToString() + ",[SLTheoDoi] =" + radGridView7.Rows[i].Cells["SLTheoDoi"].Value.ToString() + " where ID='" + radGridView7.Rows[i].Cells["ID"].Value.ToString() + "'";
                    radGridView7.Rows.RemoveAt(i);
                    cmd.CommandText = UpdateKH;
                    cmd.ExecuteNonQuery();
                }
                MessageBox.Show("Chỉnh sửa thành công");
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
            finally
            {
                Conn.Close();
                cmd.Dispose();
            }
        }

        private void TinhKH_ChiTietUuTien()
        {
           
            DataSet ds = new DataSet();
            Conn = prs.Connect_DBScansia();
            Conn.Open();
            //string GhiChu1 = null;
            try
            {
                if (cb_MaSPTinhKH.Text == "")
                {
                    if (txt_SLLKH.Text == "")
                        MessageBox.Show("Nhập đầy đủ thông tin sản phẩm và số lượng");
                    else
                    {
                        if (double.Parse(txt_SLLKH.Text) < 10)
                        {
                            GhiChu1 = @"select dbo.Load_CTSP_Mua.TenSP,dbo.Load_CTSP_Mua.TenChiTiet,qry1.SLCT_QuyDoi as SoLuongCT,qry2.TonKho AS SLTon,qry3.SoLuong,qry1.SLCT_QuyDoi*qry1.SLKH*" + txt_SLLKH.Text + " as SLLP,qry1.SLCT_QuyDoi*qry1.SLKH*" + txt_SLLKH.Text + "+isnull(qry3.SoLuong,0)-isnull(qry2.TonKho,0) as SLNhap";
                            GhiChu1 += @"        from 
                                    (select dbo.ChiTiet_KhuVuc.MaCT,ChiTiet_KhuVuc.SLCT_QuyDoi,Load_cbSP.SLKH from dbo.ChiTiet_KhuVuc inner join dbo.Load_cbSP on dbo.Load_cbSP.MaSP=ChiTiet_KhuVuc.MaSP and KhuVuc='KV1X') as qry1 left join ";
                            GhiChu1 += @"               (select dbo.KTG_TK.MaCT,TonKho from dbo.KTG_TK) as qry2 on qry1.MaCT=qry2.MaCT
                                    left join (select MaCT,sum(dbo.KH_Theodoi.SLTheoDoi) as SoLuong from dbo.KH_Theodoi where dbo.KH_Theodoi.ID_KeHoach like '%KV1X%'
                                    group by MaCT) as qry3 on qry1.MaCT=qry3.MaCT inner join dbo.Load_CTSP_Mua on dbo.Load_CTSP_Mua.MaChiTiet=qry1.MaCT where qry1.SLCT_QuyDoi*qry1.SLKH*" + txt_SLLKH.Text + "+isnull(qry3.SoLuong,0)-isnull(qry2.TonKho,0)>0 order by dbo.Load_CTSP_Mua.TenSP ";
                        }
                        else
                        {
                            GhiChu1 = @"select dbo.Load_CTSP_Mua.TenSP,dbo.Load_CTSP_Mua.TenChiTiet,qry1.SLCT_QuyDoi as SoLuongCT,qry2.TonKho AS SLTon,qry3.SoLuong,qry1.SLCT_QuyDoi*" + txt_SLLKH.Text + " as SLLP,qry1.SLCT_QuyDoi*" + txt_SLLKH.Text + "+isnull(qry3.SoLuong,0)-isnull(qry2.TonKho,0) as SLNhap";
                            GhiChu1 += @"        from 
                                    (select dbo.ChiTiet_KhuVuc.MaCT,ChiTiet_KhuVuc.SLCT_QuyDoi,Load_cbSP.SLKH from dbo.ChiTiet_KhuVuc inner join dbo.Load_cbSP on dbo.Load_cbSP.MaSP=ChiTiet_KhuVuc.MaSP and KhuVuc='KV1X') as qry1 left join ";
                            GhiChu1 += @"               (select dbo.KTG_TK.MaCT,TonKho from dbo.KTG_TK) as qry2 on qry1.MaCT=qry2.MaCT
                                    left join (select MaCT,sum(dbo.KH_Theodoi.SLTheoDoi) as SoLuong from dbo.KH_Theodoi where dbo.KH_Theodoi.ID_KeHoach like '%KV1X%'
                                    group by MaCT) as qry3 on qry1.MaCT=qry3.MaCT inner join dbo.Load_CTSP_Mua on dbo.Load_CTSP_Mua.MaChiTiet=qry1.MaCT where qry1.SLCT_QuyDoi*" + txt_SLLKH.Text + "+isnull(qry3.SoLuong,0)-isnull(qry2.TonKho,0)>0 order by dbo.Load_CTSP_Mua.TenSP ";

                        }
                    }
                }
                else
                {
                    MaSP1 = prs.substring_right(cb_MaSPTinhKH.Text, child);
                    if (MaSP1 == "")
                        MessageBox.Show("Nhập sai thông tin");
                    else
                    {
                        if (txt_SLLKH.Text == "")
                            MessageBox.Show("Nhập thiếu thông tin");
                        else
                        {
                            if (double.Parse(txt_SLLKH.Text) < 10)
                            {
                                GhiChu1 = @"select dbo.Load_CTSP_Mua.TenChiTiet,dbo.Load_CTSP_Mua.TenSP,qry1.SLCT_QuyDoi as SoLuongCT,qry2.TonKho AS SLTon,qry3.SoLuong,qry1.SLCT_QuyDoi*qry1.SLKH*" + txt_SLLKH.Text + " as SLLP,qry1.SLCT_QuyDoi*qry1.SLKH*" + txt_SLLKH.Text + "+isnull(qry3.SoLuong,0)-isnull(qry2.TonKho,0) as SLNhap";
                                GhiChu1 += @"        from 
                                    (select dbo.ChiTiet_KhuVuc.MaCT,dbo.Load_cbSP.SLKH,ChiTiet_KhuVuc.SLCT_QuyDoi from dbo.ChiTiet_KhuVuc inner join dbo.Load_cbSP on dbo.Load_cbSP.MaSP=ChiTiet_KhuVuc.MaSP and KhuVuc='KV1X' and dbo.Load_cbSP.MaSP='" + MaSP1 + "') as qry1 left join ";
                                GhiChu1 += @"               (select dbo.KTG_TK.MaCT,TonKho from dbo.KTG_TK) as qry2 on qry1.MaCT=qry2.MaCT
                                    left join (select MaCT,sum(dbo.KH_Theodoi.SLTheoDoi) as SoLuong from dbo.KH_Theodoi where dbo.KH_Theodoi.ID_KeHoach like '%KV1X%'
                                    group by MaCT) as qry3 on qry1.MaCT=qry3.MaCT inner join dbo.Load_CTSP_Mua on dbo.Load_CTSP_Mua.MaChiTiet=qry1.MaCT where qry1.SLCT_QuyDoi*qry1.SLKH*" + txt_SLLKH.Text + "+isnull(qry3.SoLuong,0)-isnull(qry2.TonKho,0)>0";
                            }
                            else
                            {
                                GhiChu1 = @"select dbo.Load_CTSP_Mua.TenChiTiet,dbo.Load_CTSP_Mua.TenSP,qry1.SLCT_QuyDoi as SoLuongCT,qry2.TonKho AS SLTon,qry3.SoLuong,qry1.SLCT_QuyDoi*" + txt_SLLKH.Text + " as SLLP,qry1.SLCT_QuyDoi*" + txt_SLLKH.Text + "+isnull(qry3.SoLuong,0)-isnull(qry2.TonKho,0) as SLNhap";
                                GhiChu1 += @"        from 
                                    (select dbo.ChiTiet_KhuVuc.MaCT,ChiTiet_KhuVuc.SLCT_QuyDoi from dbo.ChiTiet_KhuVuc where KhuVuc='KV1X' and MaSP='" + MaSP1 + "') as qry1 left join ";
                                GhiChu1 += @"               (select dbo.KTG_TK.MaCT,TonKho from dbo.KTG_TK) as qry2 on qry1.MaCT=qry2.MaCT
                                    left join (select MaCT,sum(dbo.KH_Theodoi.SLTheoDoi) as SoLuong from dbo.KH_Theodoi where dbo.KH_Theodoi.ID_KeHoach like '%KV1X%'
                                    group by MaCT) as qry3 on qry1.MaCT=qry3.MaCT inner join dbo.Load_CTSP_Mua on dbo.Load_CTSP_Mua.MaChiTiet=qry1.MaCT where qry1.SLCT_QuyDoi*" + txt_SLLKH.Text + "+isnull(qry3.SoLuong,0)-isnull(qry2.TonKho,0)>0";
                            }
                        }

                    }
                }
                //MessageBox.Show(GhiChu1);
                ds = prs.dts(GhiChu1, Conn, ds);
                prs.ExportExcel(ds.Tables[0], "C:\\Huynh\\KTG_CTUT.xlsx", 6, 1);
                ds.Clear();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
            finally
            {
                Conn.Close();
                ds.Dispose();
            }
        }
        private void bt_tinhCTUT_Click(object sender, EventArgs e)
        {
            TinhKH_ChiTietUuTien();
        }
        #endregion
        #region //Khu vuc 1
        private void cb_MaSP_KV1Search_SelectedIndexChanged(object sender, Telerik.WinControls.UI.Data.PositionChangedEventArgs e)
        {
            MaSP1 = prs.substring_right(cb_MaSP_KV1Search.Text, child);
            cb_MaCT_KV1Search.Items.Clear();
            cb_MaCT_KV1Search.Text = "";
            for (i = dataGridView_LoadSP.Rows.Count - 2; i >= 0; i--)
            {
                if (MaSP1 == dataGridView_LoadSP.Rows[i].Cells[0].Value.ToString())
                {
                    cb_MaCT_KV1Search.Items.Add(dataGridView_LoadSP.Rows[i].Cells[1].Value.ToString());
                }
            }
        }
        private void search_KTG()
        {
            Conn = prs.Connect_DBScansia();
            Conn.Open();
            try
            {
                if ((rd_Nhap_KV1Search.Checked == true && rd_Xuat_KV1Search.Checked == true) || (rd_Nhap_KV1Search.Checked == false && rd_Xuat_KV1Search.Checked == false))
                {
                    string_temp = @"SELECT Load_CTSP_Mua.TenSP,Load_CTSP_Mua.MaChiTiet,Load_CTSP_Mua.TenChiTiet,left([SoKH],5) as SoKH,[SoBB] as SoPhieu,[SLNhap],[SLXuat],convert(varchar(10),[Ngay],103) as Ngay,[FSC],convert(varchar(20),[NgayInsert],113) as NgayInsert,[UserNhap]+[UserXuat] as UserNhap,Load_CTSP_Mua.ChieuDaySC*ChieuRongSC*ChieuDaiSC*(SLNhap+SLXuat)/1000000000 as SoKhoi,[SoXe],[Serial], Row_number() over(order by Serial) STT
                            FROM [dbo].[KV1_KTG] inner join dbo.Load_CTSP_Mua on dbo.Load_CTSP_Mua.MaChiTiet=KV1_KTG.MaCT where Serial is not null";
                }
                else
                {
                    if (rd_Xuat_KV1Search.Checked == true)
                        string_temp = @"SELECT Load_CTSP_Mua.TenSP,Load_CTSP_Mua.MaChiTiet,Load_CTSP_Mua.TenChiTiet,left([SoKH],5) as SoKH,[SoBB] as SoPhieu,[SLNhap],[SLXuat],convert(varchar(10),[Ngay],103) as Ngay,[FSC],convert(varchar(20),[NgayInsert],113) as NgayInsert,[UserNhap]+[UserXuat] as UserNhap,Load_CTSP_Mua.ChieuDaySC*ChieuRongSC*ChieuDaiSC*(SLNhap+SLXuat)/1000000000 as SoKhoi,[SoXe],[Serial], Row_number() over(order by Serial) STT
                            FROM [dbo].[KV1_KTG] inner join dbo.Load_CTSP_Mua on dbo.Load_CTSP_Mua.MaChiTiet=KV1_KTG.MaCT where SLXuat<>0";
                    if (rd_Nhap_KV1Search.Checked == true)
                        string_temp = @"SELECT Load_CTSP_Mua.TenSP,Load_CTSP_Mua.MaChiTiet,Load_CTSP_Mua.TenChiTiet,left([SoKH],5) as SoKH,[SoBB] as SoPhieu,[SLNhap],[SLXuat],convert(varchar(10),[Ngay],103) as Ngay,[FSC],convert(varchar(20),[NgayInsert],113) as NgayInsert,[UserNhap]+[UserXuat] as UserNhap,Load_CTSP_Mua.ChieuDaySC*ChieuRongSC*ChieuDaiSC*(SLNhap+SLXuat)/1000000000 as SoKhoi,[SoXe],[Serial], Row_number() over(order by Serial) STT
                            FROM [dbo].[KV1_KTG] inner join dbo.Load_CTSP_Mua on dbo.Load_CTSP_Mua.MaChiTiet=KV1_KTG.MaCT where SLNhap<>0";
                }

                if (cb_MaSP_KV1Search.Text != "")
                {
                    MaSP1 = prs.substring_right(cb_MaSP_KV1Search.Text, child);
                    string_temp += @" and Load_CTSP_Mua.MaSP like '" + MaSP1 + "'";
                }
                if (cb_MaCT_KV1Search.Text != "")
                {
                    MaCT1 = prs.substring_right(cb_MaCT_KV1Search.Text, child);
                    string_temp += @" and Load_CTSP_Mua.MaChiTiet like '" + MaCT1 + "'";
                }
                if (txt_Serial_KV1Search.Text != "")
                    string_temp += @" and Serial like '%" + txt_Serial_KV1Search.Text + "%'";
                if (txt_SoKH_KV1Search.Text != "")
                    string_temp += @" and SoKH like '%" + txt_SoKH_KV1Search.Text + "%'";
                if (txt_SoPhieu_KV1Search.Text != "")
                    string_temp += @" and SoBB like '%" + txt_SoPhieu_KV1Search.Text + "%'";
                if (txt_SoXe_KV1Search.Text != "")
                    string_temp += @" and SoXe like '%" + txt_SoXe_KV1Search.Text + "%'";
                if (txt_FSC_KV1Search.Text != "")
                    string_temp += @" and FSC like '%" + txt_FSC_KV1Search.Text + "%'";

                if (txt_Datebegin__KV1Search.Text != "")
                {
                    if (txt_timebegin_KV1Search.Text == "")
                        MessageBox.Show("Bạn chưa chọn giờ");
                    else
                    {
                        Datebegin = Date_Split(txt_Datebegin__KV1Search.Text);
                        Datebegin = Datebegin + " " + txt_timebegin_KV1Search.Text;
                        string_temp += " and convert(datetime,convert(varchar(10),Ngay,101)+' '+convert(varchar(10),Ngayinsert,108))>='" + Datebegin + "'";
                    }
                }
                if (txt_Dateend_KV1Search.Text != "")
                {
                    if (txt_timeend_KV1Search.Text == "")
                        MessageBox.Show("Bạn chưa chọn giờ");
                    else
                    {
                        Dateend = Date_Split(txt_Dateend_KV1Search.Text);
                        Dateend = Dateend + " " + txt_timeend_KV1Search.Text;
                        string_temp += " and  convert(datetime,convert(varchar(10),Ngay,101)+' '+convert(varchar(10),Ngayinsert,108))<'" + Dateend + "'";
                    }

                }
                //MessageBox.Show(string_temp);
               
                DataSet ds = new DataSet();
                ds = prs.dts(string_temp, Conn, ds);

                radGridView2.DataSource = ds.Tables[0];
                SLNhap_d = 0;
                SLXuat_d = 0;
                //count = 0;
                SoKhoi_d = 0;
                for (i = 0; i < ds.Tables[0].Rows.Count; i++)
                {
                    SLNhap_d += double.Parse(ds.Tables[0].Rows[i]["SLNhap"].ToString());
                    SLXuat_d += double.Parse(ds.Tables[0].Rows[i]["SLXuat"].ToString());
                    SoKhoi_d += double.Parse(ds.Tables[0].Rows[i]["SoKhoi"].ToString());
                }
                lb_SLNhap_KV1Search.Text = string.Format("{0:###,###,###.#}", SLNhap_d);
                lb_SLXuat_KV1Search.Text = string.Format("{0:###,###,###.#}", SLXuat_d);
                lb_SoKhoi_KV1Search.Text = string.Format("{0:###,###.###}", SoKhoi_d);
                lb_SLPhieu_KV1Search.Text = string.Format("{0:###,###}", i);
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
            finally
            {
                Conn.Close();
            }
                
        }
        private void bt_SearchKV1KTG_Click(object sender, EventArgs e)
        {
            search_KTG();

        }
        private void exportexcel_KV1KTG()
        {
            if (radGridView2.Rows.Count > 1)
            {
                DataTable dt = (DataTable)radGridView2.DataSource;
                prs.ExportExcel(dt, "C:\\Huynh\\KTG_Planning.xlsx", 6, 1);
               // prs.ExportExcel(dt, @"\\192.168.1.10\SHARE\Book1.xlsx", 6, 1);
            }
            else
                MessageBox.Show("Không có dữ liệu để xuất ra excel");
        }
        private void bt_excelexport_KV1Search_Click(object sender, EventArgs e)
        {
            exportexcel_KV1KTG();
        }
        private DataSet report_search()
        {

            DataSet ds = new DataSet();
            Conn = prs.Connect_DBScansia();
            Conn.Open();
            SqlCommand cmd = new SqlCommand();

            cmd.Connection = Conn;
            cmd.CommandType = CommandType.StoredProcedure;
            string[] temp = dtp_DateBegin_KV1.Text.Split('/');
            //string[] temp = txt_NgayStart.Text.Split('/');
            Datebegin = temp[1] + "/" + temp[0] + "/" + temp[2] + " 00:00:00";
            string[] temp2 = dtp_DateEnd_KV1.Text.Split('/');
            //string[] temp2 = txt_Ngayend.Text.Split('/');
            Dateend = temp2[1] + "/" + temp2[0] + "/" + temp2[2] + " 23:59:59";
            if (rd_CTPS.Checked == false)
            {
                if (cb_MaSP_KV1TK.Text == "")
                {
                    cmd.CommandText = "prc_KTG_TH";
                    cmd.Parameters.AddWithValue("@datebegin", Datebegin);
                    cmd.Parameters.AddWithValue("@dateend", Dateend);
                }
                if (cb_MaSP_KV1TK.Text != "")
                {
                    MaSP1 = prs.substring_right(cb_MaSP_KV1TK.Text, child);
                    if (MaSP1 == "")
                        MessageBox.Show("Nhập sai thông tin SP");
                    else
                    {

                        cmd.CommandText = "prc_KTG_TH_SP";
                        cmd.Parameters.AddWithValue("@datebegin", Datebegin);
                        cmd.Parameters.AddWithValue("@dateend", Dateend);
                        cmd.Parameters.AddWithValue("@MaSP", MaSP1);
                        // 

                        /*Search = @"select qry2.MaCT,Load_CTSP_Mua.TenSP,Load_CTSP_Mua.TenChiTiet,Load_CTSP_Mua.SoLuongCT,qry2.SLNhap,qry2.SLNhap/SoLuongCT as DBNhapPS,qry2.SLXuat,qry2.SLXuat/SoLuongCT as DBXuatPS,isnull(qry2.TonDau,0) as TonDau,qry2.TonPS,qry2.TonPS/SoLuongCT as DBTonPS,(qry2.TonPS+isnull(qry2.TonDau,0) )*Load_CTSP_Mua.ChieuDaySC*Load_CTSP_Mua.ChieuRongSC*Load_CTSP_Mua.ChieuDaiSC/1000000000 as SoKhoi
                                from (select MaCT,sum(SLNhap) as SLNhap,sum(SLXuat) as SLXuat,sum(TonDau) as TonDau,sum(TonPS)as TonPS 
                                from((select MaCT,sum(SLNhap) as SLNhap,sum(SLXuat) as SLXuat,sum(SLNhap)-sum(SLXuat) as TonPS,TonDau=0  from dbo.KV1_KTG
                                where Ngay>='" + Ngay_start1 + " 00:00:00' and Ngay<='" + Ngay_End1 + " 23:59:59' Group by MaCT) union all (select MaCT,SLNhap=0,SLXuat=0,TonPS=0,sum(SLNhap)-sum(SLXuat) as TonDau from dbo.KV1_KTG where Ngay<'" + Ngay_start1 + " 00:00:00' group by MaCT)) as qry1 group by MaCT) as qry2 inner join dbo.Load_CTSP_Mua on qry2.MaCT=Load_CTSP_Mua.MaChiTiet where Load_CTSP_Mua.MaSP='" + MaSP1 + "'";*/
                        //Test
                    }
                }
                ds = prs.dts_sql(cmd, Conn, ds);
            }
            if (rd_CTPS.Checked == true)
            {
                string_temp = @"select qry1.MaCT,Load_CTSP_Mua.TenSP,Load_CTSP_Mua.TenChiTiet,Load_CTSP_Mua.SoLuongCT,qry1.SLNhap,qry1.SLNhap/SoLuongCT as DBNhapPS,qry1.SLXuat,qry1.SLXuat/SoLuongCT as DBXuatPS,isnull(qry2.SLChuyen,0) as SoLuong,qry1.TonDau,qry1.TonPS+isnull(qry2.SLChuyen,0) as TonPS,(qry1.TonPS+isnull(qry2.SLChuyen,0))/SoLuongCT as DBTonPS,(qry1.TonPS+isnull(qry2.SLChuyen,0)+qry1.TonDau) as SLTon,(qry1.TonPS+isnull(qry2.SLChuyen,0)+qry1.TonDau)/SoLuongCT as DBTon,(qry1.TonPS+isnull(qry2.SLChuyen,0)+qry1.TonDau)*Load_CTSP_Mua.ChieuDaySC*Load_CTSP_Mua.ChieuRongSC*Load_CTSP_Mua.ChieuDaiSC/1000000000 as SoKhoi
                                from (select qry11.MaCT,sum(qry11.SLNhap) as SLNhap,sum(qry11.SLXuat) as SLXuat,sum(qry11.TonDau) as TonDau,sum(qry11.TonPS) as TonPS 
                                from 
                                (select MaCT,SLNhap,SLXuat,SLNhap-SLXuat as TonPS,TonDau=0 from dbo.KV1_KTG
                                where flag=0 and Ngay>='" + Datebegin + "' and Ngay<='" + Dateend + "'";
                string_temp += @"                 union all
                                select MaCT,SLNhap=0,SLXuat=0,TonPS=0,SLNhap-SLXuat as TonDau from dbo.KV1_KTG where Ngay<'" + Datebegin + "')";
                string_temp += @"                 as qry11 group by qry11.MaCT)as qry1 left join 
                                (select MaCT,sum(SLNhap) as SLChuyen from dbo.KV1_KTG where flag=2 and Ngay>='" + Datebegin + "' and Ngay<='" + Dateend + "' group by MaCT) ";
                string_temp += @"                 as qry2 on qry1.MaCT=qry2.MaCT inner join dbo.Load_CTSP_Mua on qry1.MaCT=Load_CTSP_Mua.MaChiTiet";
                ds = prs.dts(string_temp, Conn, ds);
            }

            cmd.Dispose();
            Conn.Close();
            return ds;

        }

        private void bt_Search_KV1TK_Click(object sender, EventArgs e)
        {
            DataSet ds = new DataSet();
            try
            {
                ds = report_search();
                if (radGridView5.Rows.Count > 0)
                {
                    for (i = 0; i < this.radGridView5.ChildRows.Count; i++)
                    {
                        GridViewGroupRowInfo groupRowInfo = this.radGridView5.ChildRows[i] as GridViewGroupRowInfo;
                        groupRowInfo.HeaderText = "";
                    }
                }
                radGridView5.DataSource = ds.Tables[0];
                if (ds.Tables[0].Rows.Count > 0)
                {


                    double min = 0;
                    for (i = 0; i < this.radGridView5.ChildRows.Count; i++)
                    {
                        //this.radGridView4.ChildRows[i].Cells["Serial_KV5Search"].Value = i.ToString();

                        GridViewGroupRowInfo groupRowInfo = this.radGridView5.ChildRows[i] as GridViewGroupRowInfo;
                        if (groupRowInfo != null)
                        {
                            SoKhoi_d = 0;
                            min = double.Parse(radGridView5.ChildRows[i].ChildRows[0].Cells["DBTon_KV1TK"].Value.ToString());
                            //groupRowInfo.HeaderText = groupRowInfo.HeaderText + " " + i.ToString();
                            // MessageBox.Show(min.ToString());
                            for (int p = 0; p < this.radGridView5.ChildRows[i].ChildRows.Count; p++)
                            {
                                temp_double = double.Parse(radGridView5.ChildRows[i].ChildRows[p].Cells["SoKhoi_KV1TK"].Value.ToString());


                                if (min > temp_double)
                                {
                                    min = temp_double;

                                }
                                SoKhoi_d += temp_double;

                            }
                            //MessageBox.Show(temp_double.ToString());
                            string_temp = groupRowInfo.HeaderText + "       Đồng bộ: " + string.Format("{0:###,###,###}", min) + ",        Số khối: " + string.Format("{0:###,###.##}", SoKhoi_d);
                            groupRowInfo.HeaderText = string_temp;

                        }

                    }
                }
                else
                    MessageBox.Show("Không có dữ liệu");


            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }

        private void bt_excelexport_KV1TK_Click(object sender, EventArgs e)
        {
            if (radGridView5.Rows.Count > 1)
            {
                DataTable dt = (DataTable)radGridView5.DataSource;
                prs.ExportExcel(dt, "C:\\Huynh\\KTG_TK.xlsx", 6, 1);
            }
            else
                MessageBox.Show("Không có dữ liệu để xuất ra excel");
        }
        #endregion

        #region //KhuVuc2
        private void Search_KhoChoRap()
        {
            Conn = prs.Connect_DBScansia();
            Conn.Open();
            try
            {
                if ((rd_Nhap_KV2Search.Checked == true && rd_Xuat_KV2Search.Checked == true) || (rd_Nhap_KV2Search.Checked == false && rd_Xuat_KV2Search.Checked == false))
                {
                    string_temp = @"select dbo.Load_CTSP_Mua.TenSP,dbo.Load_CTSP_Mua.MaChiTiet,dbo.Load_CTSP_Mua.TenChiTiet,qry1.SoKH,qry1.SoPhieu,qry1.SLXP as SLNhap,qry1.SLXLR as SLXuat,CONVERT(varchar(10),Ngay,103) as Ngay,FSC,CONVERT(varchar(20),NgayInsert,113) as NgayInsert,
                                qry1.UserNhap,(qry1.SLXP-qry1.SLXLR)*ChieuDaySC*ChieuRongSC*ChieuDaiSC/1000000000 as SoKhoi,qry1.SoXe,qry1.Serial, Row_number() over(order by Serial) STT
                                from
                                (SELECT [MaChiTiet],left([SoKH],5) as SoKH,[Serial_link] as Serial,[SoPhieu],[SoXe],[SLXP],SLXLR=0,[NgayXP] as Ngay,[UserXP] as UserNhap,[NgayInsert],'' as FSC
                                  FROM [dbo].[KV2_LXP]
                                  union all
                                  select dbo.KV2_XLR.MaChiTiet,'' as SoKH,Serial,SoPhieu,SoXe,SLXP=0,SLXLR,Ngay,UserXLR as UserNhap,NgayInsert,LyDoXuat as FSC from dbo.KV2_XLR) as qry1
                                  inner join dbo.Load_CTSP_Mua on dbo.Load_CTSP_Mua.MaChiTiet=qry1.MaChiTiet where Load_CTSP_Mua.MaChiTiet is not null";
                }
                else
                {
                    if (rd_Nhap_KV2Search.Checked == true)
                    {
                        string_temp = @"select dbo.Load_CTSP_Mua.TenSP,dbo.Load_CTSP_Mua.MaChiTiet,dbo.Load_CTSP_Mua.TenChiTiet,SoKH,SoPhieu,SLNhap,SLXuat,CONVERT(varchar(10),Ngay,103) as Ngay,FSC,CONVERT(varchar(20),NgayInsert,113) as NgayInsert,UserNhap,ChieuDaySC*ChieuRongSC*ChieuDaiSC*qry1.SLNhap/1000000000 as SoKhoi,SoXe,Serial, Row_number() over(order by Serial) STT,MaSP from
                        (SELECT MaChiTiet,left(SoKH,5) as SoKH,SoPhieu,SLXP as SLNhap,
                            SLXuat=0,
                            [NgayXP] as Ngay,'' as FSC,
                            NgayInsert,UserXP as UserNhap
      
                            ,SoXe,Serial
                            FROM [dbo].[KV2_LXP]) as qry1 inner join dbo.Load_CTSP_Mua on dbo.Load_CTSP_Mua.MaChiTiet=qry1.MaChiTiet where qry1.MaChiTiet is not null";

                    }
                    if (rd_Xuat_KV2Search.Checked == true)
                    {
                        string_temp = @"SELECT Load_CTSP_Mua.TenSP,Load_CTSP_Mua.MaChiTiet,Load_CTSP_Mua.TenChiTiet,'' as SoKH,SoPhieu,SLNhap=0,SLXLR as SLXuat,
    
                                          convert(varchar(10),[Ngay],103) as Ngay,LyDoXuat as FSC,
                                          CONVERT(varchar(20),NgayInsert,113) as NgayInsert,UserXLR as UserNhap
      
                                          ,ChieuDaySC*ChieuRongSC*ChieuDaiSC*SLXLR/1000000000 as SoKhoi,SoXe,Serial, Row_number() over(order by Serial) STT
                                      FROM [dbo].[KV2_XLR] inner join dbo.Load_CTSP_Mua on dbo.Load_CTSP_Mua.MaChiTiet=KV2_XLR.MaChiTiet where Load_CTSP_Mua.MaChiTiet is not null";
                    }
                    
                }
                if (cb_MaSP_KV2Search.Text != "")
                {
                    MaSP1 = prs.substring_right(cb_MaSP_KV2Search.Text, child);
                    string_temp += @" and Load_CTSP_Mua.MaSP like '" + MaSP1 + "'";
                }
                if (cb_MaCT_KV2Search.Text != "")
                {
                    MaCT1 = prs.substring_right(cb_MaCT_KV2Search.Text, child);
                    string_temp += @" and Load_CTSP_Mua.MaChiTiet like '" + MaCT1 + "'";
                }
                if (txt_Serial_KV2Search.Text != "")
                    string_temp += @" and Serial like '%" + txt_Serial_KV2Search.Text + "%'";
                if (txt_SoKH_KV2Search.Text != "")
                    string_temp += @" and SoKH like '%" + txt_SoKH_KV2Search.Text + "%'";
                if (txt_SoPhieu_KV2Search.Text != "")
                    string_temp += @" and SoBB like '%" + txt_SoPhieu_KV2Search.Text + "%'";
                if (txt_SoXe_KV2Search.Text != "")
                    string_temp += @" and SoXe like '%" + txt_SoXe_KV2Search.Text + "%'";
               

                if (txt_Datebegin_KV2Search.Text != "")
                {
                    if (txt_Timebegin_KV2Search.Text == "")
                        MessageBox.Show("Bạn chưa chọn giờ");
                    else
                    {
                        Datebegin = Date_Split(txt_Datebegin_KV2Search.Text);
                        Datebegin = Datebegin + " " + txt_Timebegin_KV2Search.Text;
                        string_temp += " and convert(datetime,convert(varchar(10),Ngay,101)+' '+convert(varchar(10),Ngayinsert,108))>='" + Datebegin + "'";
                    }
                }
                if (txt_Dateend_KV2Search.Text != "")
                {
                    if (txt_Timeend_KV2Search.Text == "")
                        MessageBox.Show("Bạn chưa chọn giờ");
                    else
                    {
                        Dateend = Date_Split(txt_Dateend_KV2Search.Text);
                        Dateend = Dateend + " " + txt_Timeend_KV2Search.Text;
                        string_temp += " and  convert(datetime,convert(varchar(10),Ngay,101)+' '+convert(varchar(10),Ngayinsert,108))<'" + Dateend + "'";
                    }

                }
                string_temp +=" order by Serial";
               //MessageBox.Show(string_temp);
                
                DataSet ds = new DataSet();
                ds = prs.dts(string_temp, Conn, ds);
                radGridView3.DataSource = ds.Tables[0];
                if (ds.Tables[0].Rows.Count > 0)
                {
                   
                    SLNhap_d = 0;
                    SLXuat_d = 0;
                    //count = 0;
                    SoKhoi_d = 0;
                    for (i = 0; i < ds.Tables[0].Rows.Count; i++)
                    {
                        SLNhap_d += double.Parse(ds.Tables[0].Rows[i]["SLNhap"].ToString());
                        SLXuat_d += double.Parse(ds.Tables[0].Rows[i]["SLXuat"].ToString());
                        SoKhoi_d += double.Parse(ds.Tables[0].Rows[i]["SoKhoi"].ToString());
                    }
                    lb_SLNhap_KV2Search.Text = string.Format("{0:###,###,###.#}", SLNhap_d);
                    lb_SLXuat_KV2Search.Text = string.Format("{0:###,###,###.#}", SLXuat_d);
                    lb_SoKhoi_KV2Search.Text = string.Format("{0:###,###.###}", SoKhoi_d);
                    lb_SLPhieu_KV2Search.Text = string.Format("{0:###,###}", i);
                }
                else
                    MessageBox.Show("Không có dữ liệu");

                
                
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
            finally
            {
                Conn.Close();
            }
        }

        private void bt_KV2Search_Click(object sender, EventArgs e)
        {
            Search_KhoChoRap();
        }

        private void bt_excelexport_KV2Search_Click(object sender, EventArgs e)
        {
            if (radGridView3.Rows.Count > 1)
            {
                DataTable dt = (DataTable)radGridView3.DataSource;
                prs.ExportExcel(dt, "C:\\Huynh\\KhoChoRap_Planning.xlsx", 6, 1);
            }
            else
                MessageBox.Show("Không có dữ liệu để xuất ra excel");
        }
        private void cb_MaSP_KV2Search_SelectedIndexChanged(object sender, Telerik.WinControls.UI.Data.PositionChangedEventArgs e)
        {
            MaSP1 = prs.substring_right(cb_MaSP_KV2Search.Text, child);
            cb_MaCT_KV2Search.Items.Clear();
            cb_MaCT_KV2Search.Text = "";
            for (i = dataGridView_LoadSP.Rows.Count - 2; i >= 0; i--)
            {
                if (MaSP1 == dataGridView_LoadSP.Rows[i].Cells[0].Value.ToString())
                {
                    cb_MaCT_KV2Search.Items.Add(dataGridView_LoadSP.Rows[i].Cells[1].Value.ToString());
                }
            }
        }
        private DataTable dta(int n)
        {
            int j = 0;
            string colum = null;
            DataTable dt = new DataTable();
            while (j < n)
            {
                colum = j.ToString();
                dt.Columns.Add(j.ToString(), typeof(string));
                j++;
            }
            return dt;
        }
        private DataTable report(DataSet ds, int dsrows, int row_grid, int columns_grid)
        {
            i = 0;
            k = 0;
            j = 0;
            Process prs = new Process();
            SqlConnection Conn = prs.Connect();
            DataTable dt = new DataTable();
            SqlCommand cmd = new SqlCommand();
            string columnname;
            Conn.Open();
            try
            {

                cmd.Connection = Conn;
                dt = dta(columns_grid);
                int count = 0;
                while (i < row_grid)
                {
                    DataRow dtrow = dt.NewRow();
                    j = 0;
                    columnname = j.ToString();
                    dtrow[columnname] = ds.Tables[0].Rows[count][0].ToString();

                    j++;
                    k = count;
                    while (k < dsrows)
                    {
                        if (k < dsrows - 1)
                        {

                            if (ds.Tables[0].Rows[k][0].ToString() == ds.Tables[0].Rows[k + 1][0].ToString())
                            {
                                for (int m = 1; m < ds.Tables[0].Columns.Count; m++)
                                {
                                    columnname = j.ToString();
                                    dtrow[columnname] = ds.Tables[0].Rows[k][m].ToString();
                                    //MessageBox.Show(dtrow["4"].ToString());
                                    j++;
                                }
                                k++;
                            }
                            if (ds.Tables[0].Rows[k][0].ToString() != ds.Tables[0].Rows[k + 1][0].ToString())
                            {
                                for (int m = 1; m < ds.Tables[0].Columns.Count; m++)
                                {
                                    columnname = j.ToString();
                                    dtrow[columnname] = ds.Tables[0].Rows[k][m].ToString();
                                    //MessageBox.Show(dtrow["4"].ToString());
                                    j++;
                                }

                                //MessageBox.Show("No Khac Rui Kia");
                                while (j < columns_grid)
                                {
                                    //
                                    columnname = j.ToString();
                                    dtrow[columnname] = "";//chinh o day
                                    j++;
                                }
                                count = k + 1;
                                dt.Rows.Add(dtrow);

                                j = 0;
                                break;

                            }
                        }
                        if (k == dsrows - 1)
                        {
                            if (ds.Tables[0].Rows[k][0].ToString() == ds.Tables[0].Rows[k - 1][0].ToString())
                            {
                                for (int m = 1; m < ds.Tables[0].Columns.Count; m++)
                                {
                                    columnname = j.ToString();
                                    dtrow[columnname] = ds.Tables[0].Rows[k][m].ToString();
                                    //MessageBox.Show(dtrow["4"].ToString());
                                    j++;
                                }
                                k++;
                            }
                            if (ds.Tables[0].Rows[k][0].ToString() != ds.Tables[0].Rows[k - 1][0].ToString())
                            {
                                for (int m = 1; m < ds.Tables[0].Columns.Count; m++)
                                {
                                    columnname = j.ToString();
                                    dtrow[columnname] = ds.Tables[0].Rows[k][m].ToString();
                                    //MessageBox.Show(dtrow["4"].ToString());
                                    j++;
                                }

                                //MessageBox.Show("No Khac Rui Kia");
                                while (j < columns_grid)
                                {
                                    //
                                    columnname = j.ToString();
                                    dtrow[columnname] = "";// chinh o day
                                    j++;
                                }
                                count = k + 1;
                                dt.Rows.Add(dtrow);
                                //MessageBox.Show(dt.Rows[0][18].ToString());
                                j = 0;
                                i = row_grid;
                                break;
                            }
                        }
                    }

                    i++;
                }
                // MessageBox.Show(dt.Rows[20][4].ToString());
                //dataGridView1.DataSource = dt;



            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
            finally
            {
                Conn.Close();
                cmd.Dispose();
            }
            return dt;
        }
        private void TD_LXP()
        {
            
            DataSet ds = new DataSet();
            DataTable dt = new DataTable();
            Conn = prs.Connect_DBScansia();
            Conn.Open();
            SqlCommand cmd = new SqlCommand();
            try
            {
                cmd.Connection = Conn;
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.CommandText = "prc_TDKHXP";
                if (cb_MaSP_KV2Search.Text == "")
                {
                    cmd.Parameters.AddWithValue("@MaSP", "test");
                    cmd.Parameters.AddWithValue("@check", "0");

                }
                if (cb_MaSP_KV2Search.Text != "")
                {
                    MaSP1 = prs.substring_right(cb_MaSP_KV2Search.Text, child);
                    if (MaSP1 == "")
                        MessageBox.Show("Nhập sai thông tin SP");
                    else
                    {
                        cmd.Parameters.AddWithValue("@check", "1");
                        cmd.Parameters.AddWithValue("@MaSP", MaSP1);
                        //ds = prs.dts_sql(cmd, Conn, ds);
                    }
                }
                ds = prs.dts_sql(cmd, Conn, ds);
                
                if (ds.Tables[0].Rows.Count > 0)
                {
                    int count = 0;

                    for (i = 0; i < ds.Tables[0].Rows.Count - 1; i++)
                    {
                        if (ds.Tables[0].Rows[i][0].ToString() != ds.Tables[0].Rows[i + 1][0].ToString())
                            count++;
                    }
                    dt = report(ds, ds.Tables[0].Rows.Count - 1, count, 100);
                    //MessageBox.Show(dt.Rows.Count.ToString(), ds.Tables[0].Rows[0][0].ToString());
                    if (cb_MaSP_KV2Search.Text == "")
                    {
                        prs.ExportExcel(dt, "C:\\Huynh\\KV2_LXP.xls", 6, 2);
                    }
                    else
                        prs.ExportExcel(dt, "C:\\Huynh\\KV2_LXP_SP.xls", 6, 2);
                    ds.Clear();
                    dt.Clear();
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
            finally
            {
                Conn.Close();
                cmd.Dispose();
            }

        }
       

        #endregion

        #region //Kho thanh pham
        private void Search_KTP()
        {
            Conn = prs.Connect_DBScansia();
            Conn.Open();
            DataSet ds = new DataSet();
            try
            {
                if (chk_TonKho_KV5.Checked == true)
                {
                    rd_Nhap_KV5Search.Checked = false;
                    rd_Xuat_KV5Search.Checked = false;
                }
            if ((rd_Nhap_KV5Search.Checked == true && rd_Xuat_KV5Search.Checked == true) || (rd_Nhap_KV5Search.Checked == false && rd_Xuat_KV5Search.Checked == false))
            {
                if (chk_TonKho_KV5.Checked == false)
                {
                    string_temp = @"select qry1.MaSP,Load_cbSP.TenSP,qry1.ArticleNumber,qry1.IDNumber,qry1.SLNhap,qry1.SLXuat,CONVERT(varchar(10),qry1.Ngay,103) as Ngay,convert(varchar(20),qry1.NgayInsert,113) as NgayInsert,KhoTP_chungTu.SoBB,qry1.LyDo,qry1.ViTri,qry1.Kien,KhoTP_chungTu.MaKH as KhachHang,qry1.LoaiGo,qry1.UserNhap from
                            (select ChungTu,dbo.KhoTP_NK.MaSP,KhoTP_NK.ArticleNumber,KhoTP_NK.IDNumber,KhoTP_NK.SLNhap,SLXuat=0,KhoTP_NK.Ngay,SoBB,KhoTP_NK.LyDo,KhoTP_NK.ViTri,KhoTP_NK.Kien,KhoTP_NK.LoaiGo,KhoTP_NK.Serial,KhoTP_NK.UserNhap,NgayInsert
                            from dbo.KhoTP_NK
                            union all
                            select ChungTu,dbo.KhoTP_XK.MaSP,KhoTP_XK.ArticleNumber,KhoTP_XK.IDNumber,SLNhap=0,KhoTP_XK.SLXuat,KhoTP_XK.Ngay,SoBB,KhoTP_XK.LyDo,KhoTP_XK.ViTri,'' as Kien,KhoTP_XK.LoaiGo,KhoTP_XK.Serial,KhoTP_XK.UserNhap,NgayInsert
                            from dbo.KhoTP_XK) as  qry1 inner join KhoTP_ChungTu on KhoTP_ChungTu.MaCT=qry1.ChungTu inner join dbo.Load_cbSP on Load_cbSP.MaSP=qry1.MaSP where Load_cbSP.MaSP is not null ";
                }
                if (chk_TonKho_KV5.Checked == true)
                {
                    
                    txt_Datebegin_KV5Search.Text = "";
                    txt_Dateend_KV5Search.Text = "";
                    string_temp = @"select qry1.* from (SELECT [MaSP],[TenSP],[ArticleNumber],[IDNumber],[SLNhap],SLXuat=0 ,convert(varchar(10),[Ngay],103) as Ngay
                                        ,NgayInsert='',[SoBB],[LyDo],ViTri,[Kien],[KhachHang],[LoaiGo],UserNhap=''
                                          FROM [DataBase_ScansiaPacific2013].[dbo].[KhoTP_SPChuaXuat]) as qry1 where MaSP is not null ";
                }
            }
            else
            {
                if (rd_Nhap_KV5Search.Checked == true)
                    string_temp = @"select qry1.MaSP,Load_cbSP.TenSP,qry1.ArticleNumber,qry1.IDNumber,qry1.SLNhap,qry1.SLXuat,CONVERT(varchar(10),qry1.Ngay,103) as Ngay,convert(varchar(20),qry1.NgayInsert,113) as NgayInsert,KhoTP_chungTu.SoBB,qry1.LyDo,qry1.ViTri,qry1.Kien,KhoTP_chungTu.MaKH as KhachHang,qry1.LoaiGo,qry1.UserNhap from
                            (select ChungTu,dbo.KhoTP_NK.MaSP,KhoTP_NK.ArticleNumber,KhoTP_NK.IDNumber,KhoTP_NK.SLNhap,SLXuat=0,KhoTP_NK.Ngay,SoBB,KhoTP_NK.LyDo,KhoTP_NK.ViTri,KhoTP_NK.Kien,KhoTP_NK.LoaiGo,KhoTP_NK.Serial,NgayInsert,UserNhap
                            from dbo.KhoTP_NK) as  qry1 inner join KhoTP_ChungTu on KhoTP_ChungTu.MaCT=qry1.ChungTu inner join dbo.Load_cbSP on Load_cbSP.MaSP=qry1.MaSP where Load_cbSP.MaSP is not null";
                if (rd_Xuat_KV5Search.Checked == true)
                    string_temp = @"select qry1.MaSP,Load_cbSP.TenSP,qry1.ArticleNumber,qry1.IDNumber,qry1.SLNhap,qry1.SLXuat,CONVERT(varchar(10),qry1.Ngay,103) as Ngay,convert(varchar(20),qry1.NgayInsert,113) as NgayInsert,KhoTP_chungTu.SoBB,qry1.LyDo,qry1.ViTri,qry1.Kien,KhoTP_chungTu.MaKH as KhachHang,qry1.LoaiGo,qry1.UserNhap from
                            (select ChungTu,dbo.KhoTP_XK.MaSP,KhoTP_XK.ArticleNumber,KhoTP_XK.IDNumber,SLNhap=0,KhoTP_XK.SLXuat,KhoTP_XK.Ngay,SoBB,KhoTP_XK.LyDo,KhoTP_XK.ViTri,'' as Kien,KhoTP_XK.LoaiGo,KhoTP_XK.Serial,KhoTP_XK.NgayInsert,UserNhap
                            from dbo.KhoTP_XK) as  qry1 inner join KhoTP_ChungTu on KhoTP_ChungTu.MaCT=qry1.ChungTu inner join dbo.Load_cbSP on Load_cbSP.MaSP=qry1.MaSP where Load_cbSP.MaSP is not null";
            }
            if(cb_MaSP_KV5Search.Text != "")
            {
                MaSP1 = prs.substring_right(cb_MaSP_KV5Search.Text, child);
                if(chk_TonKho_KV5.Checked==false)

                    string_temp += @" and Load_cbSP.MaSP like '" + MaSP1 + "'";
                if(chk_TonKho_KV5.Checked==true)
                    string_temp += @" and MaSP like '" + MaSP1 + "'";
            }

             if (txt_ArtNo_KV5Search.Text != "")
                 string_temp += @" and ArticleNumber like '%" + txt_ArtNo_KV5Search.Text + "%'";
             if (cb_ViTri_KV5Search.Text != "")
                 string_temp += @"and Vitri like '%" + cb_ViTri_KV5Search.Text + "%'" ;
             if (txt_SoBB_KV5Search.Text != "")
             {
                
                 string_temp += @" and KhoTP_ChungTu.SoBB like '%" + txt_SoBB_KV5Search.Text + "%'";
             }

            if (txt_Datebegin_KV5Search.Text != "")
            {
                    
                   
                    Datebegin = Date_Split(txt_Datebegin_KV5Search.Text);
                    //Datebegin = Datebegin + " " + txt_Timebegin_KV2Search.Text;
                    string_temp += " and qry1.Ngay>='" + Datebegin + " 00:00'";
                    
            }
            if (txt_Dateend_KV5Search.Text != "")
            {
                    
                    Dateend = Date_Split(txt_Dateend_KV5Search.Text);
                    //Dateend = Dateend + " " + txt_Timeend_KV2Search.Text;
                    string_temp += " and qry1.Ngay<='" + Dateend + " 23:59'";
                    

            }
            if (cb_LyDo_KV5Search.Text != "")
            {
                string_temp += " and qry1.LyDo like'%" + prs.substring_right(cb_LyDo_KV5Search.Text, child) + "%'";
            }
            string_temp +=" order by qry1.NgayInsert";
           // MessageBox.Show(string_temp);
            
            ds = prs.dts(string_temp, Conn, ds);
            if (radGridView4.Rows.Count > 0)
            {
                for (i = 0; i < this.radGridView4.ChildRows.Count; i++)
                {
                    GridViewGroupRowInfo groupRowInfo = this.radGridView4.ChildRows[i] as GridViewGroupRowInfo;
                    groupRowInfo.HeaderText = "";
                }
            }
            radGridView4.DataSource = ds.Tables[0];
            if (ds.Tables[0].Rows.Count > 0)
            {
                   
                SLNhap_d = 0;
                SLXuat_d = 0;
                //count = 0;
                SoKhoi_d = 0;
                for (i = 0; i < ds.Tables[0].Rows.Count; i++)
                {
                    SLNhap_d += double.Parse(ds.Tables[0].Rows[i]["SLNhap"].ToString());
                    SLXuat_d += double.Parse(ds.Tables[0].Rows[i]["SLXuat"].ToString());
                    //SoKhoi_d += double.Parse(ds.Tables[0].Rows[i]["SoKhoi"].ToString());
                    SoKhoi_d = SLNhap_d - SLXuat_d;
                }
                lb_SLNhap_KV5Search.Text = string.Format("{0:###,###,###.#}", SLNhap_d);
                lb_SLXuat_KV5Search.Text = string.Format("{0:###,###,###.#}", SLXuat_d);
                lb_SLTon_KV5Search.Text = string.Format("{0:###,###.###}", SoKhoi_d);
                lb_SLPallet_KV5Search.Text = string.Format("{0:###,###}", i);
                for (i = 0; i < this.radGridView4.ChildRows.Count; i++)
                {
                    //this.radGridView4.ChildRows[i].Cells["Serial_KV5Search"].Value = i.ToString();

                    GridViewGroupRowInfo groupRowInfo = this.radGridView4.ChildRows[i] as GridViewGroupRowInfo;
                    if (groupRowInfo != null)
                    {
                        SLNhap_d = 0;
                        SLXuat_d = 0;
                        //groupRowInfo.HeaderText = groupRowInfo.HeaderText + " " + i.ToString();
                        for (int p = 0; p < this.radGridView4.ChildRows[i].ChildRows.Count; p++)
                        {
                            //this.radGridView4.ChildRows[i].ChildRows[p].Cells["Serial_KV5Search"].Value = p.ToString();
                            SLNhap_d += double.Parse(radGridView4.ChildRows[i].ChildRows[p].Cells["SLNhap_KV5Search"].Value.ToString());
                            SLXuat_d += double.Parse(radGridView4.ChildRows[i].ChildRows[p].Cells["SLXuat_KV5Search"].Value.ToString());
                        }
                        string_temp = groupRowInfo.HeaderText + "            SL Nhập: " + SLNhap_d.ToString() + "            SL Xuất: " + SLXuat_d.ToString();
                        groupRowInfo.HeaderText = string_temp;

                    }
                }
            }
            else
                MessageBox.Show("Không có dữ liệu");

                
            }
            
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
            finally
            {
                Conn.Close();
            }
        }
        private void bt_KV5Search_Click(object sender, EventArgs e)
        {
            Search_KTP();
            //sum_SLNK();
        }

        private void bt_excelexport_KV5Search_Click(object sender, EventArgs e)
        {
            if (radGridView4.Rows.Count > 1)
            {
                DataTable dt = (DataTable)radGridView4.DataSource;
                prs.ExportExcel(dt, "C:\\Huynh\\KhoTP_export.xlsx", 6, 1);
            }
            else
                MessageBox.Show("Không có dữ liệu để xuất ra excel");
        }
        private DataSet TonKho_KV5TK(SqlConnection Conn)
        {
                SqlCommand cmd = new SqlCommand();
                cmd.CommandType = CommandType.StoredProcedure;
               // SqlCommand cmd1 = new SqlCommand();
                DataSet ds = new DataSet();
                //GhiChu1 = "Các số lượng nhập xuất trong báo cáo đã trừ số lượng lập phiếu";
                cmd.Connection = Conn;
                string[] temp = dtp_Datebegin_KV5TK.Text.Split('/');
                Datebegin = temp[1] + "/" + temp[0] + "/" + temp[2];
                string[] temp2 = dtp_Dateend_KV5TK.Text.Split('/');
                Dateend = temp2[1] + "/" + temp2[0] + "/" + temp2[2];
                //cmd1.Connection = Conn;
                if (cb_MaSP_KV5TK.Text == "")
                {
                    cmd.Parameters.AddWithValue("@check", "0");
                    cmd.Parameters.AddWithValue("@MaSP", "0");
                }
                else
                {
                    MaSP1 = prs.substring_right(cb_MaSP_KV5TK.Text, child);
                    if (MaSP1 == "")
                        MessageBox.Show("Nhập sai thông tin sản phẩm");
                    else
                    {
                        cmd.Parameters.AddWithValue("@check", "1");
                        cmd.Parameters.AddWithValue("@MaSP", MaSP1);
                    }
                }

                if (rd_LyDo_KV5TonKho.Checked == false)
                {
                   
                    cmd.Parameters.AddWithValue("@datebegin", Datebegin);
                    cmd.Parameters.AddWithValue("@dateend", Dateend);
                    cmd.CommandText = "prc_KTP_TH_Ngay";
                    ds = prs.dts_sql(cmd,Conn,ds);
                   
                }
                if (rd_LyDo_KV5TonKho.Checked == true)
                {
                    string_temp = @"select dbo.Load_cbSP.TenSP,qry2.*,qry2.SLNhap-qry2.SLXuat as TonPS,qry2.DBNhap-qry2.DBXuat as DBTonPS,qry2.TonDau+qry2.SLNhap-qry2.SLXuat as SLTon,qry2.DBTonDau+qry2.DBNhap-qry2.DBXuat as DBTonCuoi from 
                    (select qry1.MaSP,qry1.LyDo,sum(qry1.TonDau)as TonDau,sum(qry1.DBTonDau) as DBTonDau,SUM(qry1.SLNhap) as SLNhap,SUM(qry1.SLXuat)as SLXuat,SUM(SLThungNhap) as DBNhap,SUM(SLThungXuat)as DBXuat
                    from(
                    select MaSP,LyDo,TonDau=0,DBTonDau=0,SUM(SLNhap) as SLNhap,SUM(SLXuat) as SLXuat,SUM(SLThungNhap) as SLThungNhap,SUM(SLThungXuat) as SLThungXuat from dbo.KhoTP 
                    where Ngay>='" + Datebegin + " 00:00' and Ngay<='" + Dateend + " 23:59' group by MaSP,LyDo";
                    string_temp += @" union all
                    select MaSP,N'Tồn đầu' as LyDo,SUM(SLNhap)-SUM(SLXuat) as TonDau,sum(SLThungNhap)-sum(SLThungXuat) as DBTonDau,SLNhap=0,SLXuat=0,SLThungNhap=0,SLThungXuat=0 from dbo.KhoTP where Ngay<'" + Datebegin + " 00:00' group by MaSP) as qry1";
                    string_temp +=@" group by qry1.MaSP,qry1.LyDo)as qry2 inner join dbo.Load_cbSP on dbo.Load_cbSP.MaSP=qry2.MaSP";
                    ds=prs.dts(string_temp,Conn,ds);
                }
                cmd.Dispose();
                Conn.Close();
                return ds;
            
        }
        private void bt_Search_KV5TK_Click(object sender, EventArgs e)
        {
            Conn = prs.Connect_DBScansia();
            Conn.Open();

            try
            {
                DataSet ds = TonKho_KV5TK(Conn);

                radGridView6.DataSource = ds.Tables[0];

                if (ds.Tables[0].Rows.Count == 0)
                    MessageBox.Show("Không có dữ liệu");

            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
            finally
            {
                Conn.Close();
                
            }

        }

        private void bt_exportexcel_KV5TK_Click(object sender, EventArgs e)
        {
            if (radGridView6.Rows.Count > 1)
            {
                DataTable dt = (DataTable)radGridView6.DataSource;
                prs.ExportExcel(dt, "C:\\Huynh\\KhoTP_TonKho.xlsx", 6, 1);
            }
            else
                MessageBox.Show("Không có dữ liệu để xuất ra excel");
        }
        
        #endregion
        #region
        private void Search_KV3()
        {
            Conn = prs.Connect_DBScansia();
            SqlCommand cmd = new SqlCommand();
            Conn.Open();
            DataSet ds = new DataSet();
            try
            {
                string_temp = @"SELECT  TenSP,[MaCT],TenChiTiet,SoKH,SoBB as SoPhieu,isnull([SLNhap],0) as SLNhap,isnull([SLXuat],0) as SLXuat,[Ngay],[LyDo],[NgayInsert],Serial
                         FROM [dbo].[KV4_KTam] inner join dbo.Load_CTSP_Mua on dbo.Load_CTSP_Mua.MaChiTiet=KV4_KTam.MaCT ";
                if ((chk_input_KV3.Checked == true && chk_output_KV3.Checked == true) || (chk_input_KV3.Checked == false && chk_output_KV3.Checked == false))
                {
                    string_temp = string_temp+" ";
                }
                else
                {
                    if (chk_input_KV3.Checked == true)
                    {
                        string_temp += " where SLNhap is not null";
                    }
                    if(chk_output_KV3.Checked==true)
                    {
                        string_temp +="where SLXuat is not null";
                    }
                }
                    if(cb_MaSP_KV3.Text!="")
                        string_temp +=" and dbo.Load_CTSP_Mua.MaSP='"+prs.substring_right(cb_MaSP_KV3.Text,child)+"'";
                    if (cb_MaCT_KV3.Text != "")
                        string_temp += " and dbo.Load_CTSP_Mua.MaChiTiet='"+prs.substring_right(cb_MaCT_KV3.Text,child)+"'";
                    if (txt_SoKH_KV3.Text != "")
                        string_temp += " and SoKH like '%"+txt_SoKH_KV3.Text+"%'";
                    if (txt_SoPhieu_KV3.Text != "")
                        string_temp += " and SoBB like '%" + txt_SoPhieu_KV3.Text + "%'";
                    if (txt_Serial_KV3.Text != "")
                        string_temp += " and dbo.KV4_KTam.Serial="+txt_Serial_KV3.Text;
                    if (txt_Datebegin_KV3.Text != "")
                    {
                        if (txt_Datebegin_KV3.Text == "")
                            MessageBox.Show("Bạn chưa chọn giờ");
                        else
                        {
                            Datebegin = Date_Split(txt_Datebegin_KV3.Text);
                            Datebegin = Datebegin + " " + txt_minutebegin_KV3.Text;
                            string_temp += " and convert(varchar(10),Ngay,101)+' '+convert(varchar(10),Ngayinsert,108)>='" + Datebegin + "'";
                        }
                    }
                    if (txt_DateEnd_KV3.Text != "")
                    {
                        if (txt_DateEnd_KV3.Text == "")
                            MessageBox.Show("Bạn chưa chọn giờ");
                        else
                        {
                            Dateend = Date_Split(txt_DateEnd_KV3.Text);
                            Dateend = Dateend + " " + txt_minuteend_KV3.Text;
                            string_temp += " and  convert(varchar(10),Ngay,101)+' '+convert(varchar(10),Ngayinsert,108)<'" + Dateend + "'";
                        }

                    }
                    string_temp += " order by Serial";
                    // MessageBox.Show(string_temp);
                       

                
                ds = prs.dts(string_temp, Conn, ds);
                radGridView9.DataSource = ds.Tables[0];
                if (ds.Tables[0].Rows.Count > 0)
                {

                    SLNhap_d = 0;
                    SLXuat_d = 0;
                    //count = 0;
                    SoKhoi_d = 0;
                    for (i = 0; i < ds.Tables[0].Rows.Count; i++)
                    {
                        SLNhap_d += double.Parse(ds.Tables[0].Rows[i]["SLNhap"].ToString());
                        SLXuat_d += double.Parse(ds.Tables[0].Rows[i]["SLXuat"].ToString());
                        //SoKhoi_d += double.Parse(ds.Tables[0].Rows[i]["SoKhoi"].ToString());
                    }
                    lb_SLNhap_KV3.Text = string.Format("{0:###,###,###.#}", SLNhap_d);
                    lb_SLXuat_KV3.Text = string.Format("{0:###,###,###.#}", SLXuat_d);
                    //lb_SoKhoi_KV2Search.Text = string.Format("{0:###,###.###}", SoKhoi_d);
                    lb_SoPhieu_KV3.Text = string.Format("{0:###,###}", i);
                }
                else
                    MessageBox.Show("Không có dữ liệu");
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
            finally
            {
                Conn.Close();
                cmd.Dispose();
            }
        }
        private void bt_Search_KV3_Click(object sender, EventArgs e)
        {
            Search_KV3();
        }
        private void cb_MaSP_KV3_SelectedIndexChanged(object sender, Telerik.WinControls.UI.Data.PositionChangedEventArgs e)
        {
            MaSP1 = prs.substring_right(cb_MaSP_KV3.Text, child);
            cb_MaCT_KV3.Items.Clear();
            cb_MaCT_KV3.Text = "";
            for (i = dataGridView_LoadSP.Rows.Count - 2; i >= 0; i--)
            {
                if (MaSP1 == dataGridView_LoadSP.Rows[i].Cells[0].Value.ToString())
                {
                    cb_MaCT_KV3.Items.Add(dataGridView_LoadSP.Rows[i].Cells[1].Value.ToString());
                }
            }
        }
        private DataSet report_searchKV3()
        {
            
            DataSet ds = new DataSet();
            Conn = prs.Connect_DBScansia();
            Conn.Open();
            SqlCommand cmd = new SqlCommand();

            cmd.Connection = Conn;
            cmd.CommandType = CommandType.StoredProcedure;
            string[] temp = dtp_DateBegin_KV3TK.Text.Split('/');
            Datebegin = temp[1] + "/" + temp[0] + "/" + temp[2] + " 00:00:00";
            string[] temp2 = dtp_DateEnd_KV3TK_.Text.Split('/');
            Dateend = temp2[1] + "/" + temp2[0] + "/" + temp2[2] + " 23:59:59";
            cmd.CommandText = "prc_KV3_TH";
            if (cb_MaSP_ReportKV3.Text == "")
            {
                cmd.Parameters.AddWithValue("@datebegin", Datebegin);
                cmd.Parameters.AddWithValue("@dateend", Dateend);
                cmd.Parameters.AddWithValue("@check", "0");
                cmd.Parameters.AddWithValue("@MaSP", "0");
            }
            if (cb_MaSP_ReportKV3.Text != "")
            {
                MaSP1 = prs.substring_right(cb_MaSP_ReportKV3.Text, child);
                if (MaSP1 == "")
                    MessageBox.Show("Nhập sai thông tin SP");
                else
                {
                    cmd.Parameters.AddWithValue("@datebegin", Datebegin);
                    cmd.Parameters.AddWithValue("@dateend", Dateend);
                    cmd.Parameters.AddWithValue("@check", "1");
                    cmd.Parameters.AddWithValue("@MaSP", MaSP1);
                }
            }
            ds = prs.dts_sql(cmd, Conn, ds);
            Conn.Close();
            cmd.Dispose();
            return ds;
        }
        private void bt_Excelexport_KV3_Click(object sender, EventArgs e)
        {
            if (radGridView9.Rows.Count > 1)
            {
                DataTable dt = (DataTable)radGridView9.DataSource;
                prs.ExportExcel(dt, "C:\\Huynh\\KhoTam_Export.xlsx", 6, 1);
            }
            else
                MessageBox.Show("Không có dữ liệu để xuất ra excel");
        }
        
        private void bt_search_TKKV3_Click(object sender, EventArgs e)
        {
            DataSet ds = new DataSet();
            try
            {
                
                ds = report_searchKV3();
                if (ds.Tables[0].Rows.Count > 0)
                {
                    if (radGridView10.Rows.Count > 0)
                    {
                        for (i = 0; i < this.radGridView10.ChildRows.Count; i++)
                        {
                            GridViewGroupRowInfo groupRowInfo = this.radGridView10.ChildRows[i] as GridViewGroupRowInfo;
                            groupRowInfo.HeaderText = "";
                        }
                    }
                    radGridView10.DataSource = ds.Tables[0];
                    if (ds.Tables[0].Rows.Count > 0)
                    {


                        double min = 0;
                        for (i = 0; i < this.radGridView10.ChildRows.Count; i++)
                        {
                            //this.radGridView4.ChildRows[i].Cells["Serial_KV5Search"].Value = i.ToString();

                            GridViewGroupRowInfo groupRowInfo = this.radGridView10.ChildRows[i] as GridViewGroupRowInfo;
                            if (groupRowInfo != null)
                            {
                                SoKhoi_d = 0;
                                min = double.Parse(radGridView10.ChildRows[i].ChildRows[0].Cells["DBTon_KV3TK"].Value.ToString());
                                //groupRowInfo.HeaderText = groupRowInfo.HeaderText + " " + i.ToString();
                                // MessageBox.Show(min.ToString());
                                for (int p = 0; p < this.radGridView10.ChildRows[i].ChildRows.Count; p++)
                                {
                                    temp_double = double.Parse(radGridView10.ChildRows[i].ChildRows[p].Cells["SoKhoi_KV3TK"].Value.ToString());
                                    if (min > temp_double)
                                    {
                                        min = temp_double;

                                    }
                                    SoKhoi_d += temp_double;

                                }
                                //MessageBox.Show(temp_double.ToString());
                                string_temp = groupRowInfo.HeaderText + "       Đồng bộ: " + string.Format("{0:###,###,###}", min) + ",        Số khối: " + string.Format("{0:###,###.##}", SoKhoi_d);
                                groupRowInfo.HeaderText = string_temp;

                            }

                        }
                    }
                    else
                        MessageBox.Show("Không có dữ liệu");


                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
           

            

        }

        private void bt_exportexcel_TKKV3_Click(object sender, EventArgs e)
        {
            if (radGridView10.Rows.Count > 1)
            {
                DataTable dt = (DataTable)radGridView10.DataSource;
                prs.ExportExcel(dt, "C:\\Huynh\\KV4_KTamTK.xlsx", 6, 1);
            }
            else
                MessageBox.Show("Không có dữ liệu để xuất ra excel");
        }
        private void TD_KeHoach()
        {
            Conn = prs.Connect_DBScansia();
            Conn.Open();
            DataSet ds = new DataSet();
            try
            {
                string selectKH = @"select ChiTietSP.TenSP,ChiTietSP.TenChiTiet,KV4_SLXuat.NgayXP,left(KH_TheoDoi.ID_KeHoach,5) as MaKH,KH_TheoDoi.SLKH as SoLuong,isnull(KV4_SLXuat.SLXuat,0) as SLXuat,KH_TheoDoi.SLKH-isnull(KV4_SLXuat.SLXuat,0) as SLTon 
                                from (select distinct(ID_KeHoach) as SoKH from dbo.KH_TheoDoi where SLTheoDoi>0 and ID_KeHoach like '%KV3%') as KH_Chon inner join dbo.KH_TheoDoi on KH_TheoDoi.ID_KeHoach=KH_Chon.SoKH
                                left join (select KV4_KTam.SoKH,sum(SLXuat) as SLXuat,min(NgayInsert) as NgayXP from dbo.KV4_KTam where SoKH is not null group by SoKH) as KV4_SLXuat on KV4_SLXuat.SoKH=KH_TheoDoi.ID 
                                inner join ChiTietSP on ChiTietSP.MaChiTiet=KH_TheoDoi.MaCT";

                ds = prs.dts(selectKH, Conn, ds);
                Fr_Report FrR = new Fr_Report();
                //Ngay1 = txt_Ngayinput.Text;
                FrR.report_TDKH_Nhung(ds);
                FrR.Show();
                
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
            finally
            {
                Conn.Close();
                ds.Clear();
            }

        }
        private void search_KH()
        {
            DataSet ds = new DataSet();
            Conn = prs.Connect_DBScansia();
            Conn.Open();
            try
            {
                string_temp = @"select ChiTietSP.TenSP,ChiTietSP.TenChiTiet,left(KH_TheoDoi.ID_KeHoach,5) as MaKH,qry1.NgayXP,qry1.SLXuat,KH_TheoDoi.SLKH as SoLuong,KH_TheoDoi.SLKH-isnull(qry1.SLXuat,0) as SLTon
                                from 
                                KH_TheoDoi left join (select SoKH,sum(SLXuat) as SLXuat,min(NgayInsert) as NgayXP from dbo.KV4_KTam group by SoKH) as qry1 on KH_TheoDoi.ID=qry1.SoKH 
                                inner join ChiTietSP on KH_TheoDoi.MaCT=ChiTietSP.MaChiTiet where KH_TheoDoi.ID_KeHoach like '%KV3%'";
                // Process prs = new Process();

                if (txt_searchKH.Text == "")
                {
                    ds = prs.dts(string_temp, Conn, ds);

                }
                else
                {
                    string_temp += " and KH_TheoDoi.ID_KeHoach like '%" + txt_searchKH.Text + "%'";
                    //MessageBox.Show(string_temp);
                    ds = prs.dts(string_temp, Conn, ds);
                }
                Fr_Report FrR = new Fr_Report();
                //Ngay1 = txt_Ngayinput.Text;
                FrR.report_TDKH_Nhung(ds);
                FrR.Show();
                //ds.Clear();
            }

            catch
            {
                MessageBox.Show("Kiểm tra lại thông tin tìm kiếm");
            }
            finally
            {
                Conn.Close();
                ds.Clear();
            }
        }
        private void bt_printf_KH_Click(object sender, EventArgs e)
        {
            search_KH();
        }
        #endregion
        #region //Query Design
        private void Load_Table(RadTreeView tree)
        {
            Conn = prs.Connect_DBScansia();
            Conn.Open();
            //DataSet ds = new DataSet();
            SqlDataReader rd = null;
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = Conn;
            try
            {
               
                string_temp = @"select * from dbo.Table_Data order by [TableName],Parent ";
                cmd.CommandText = string_temp;
                rd = cmd.ExecuteReader();
                RadTreeNode root = null;
                root = tree.Nodes.Add("Table Structure");
                while (rd.Read())
                {
                    
                    if (rd["Parent"].ToString() == "1")
                    {
                        root = tree.Nodes.Add(rd["TableName"].ToString());

                    }
                    else
                    {
                        root.Nodes.Add(rd["TableColumns"].ToString());
                    }
                        //MessageBox.Show("aaaa");
                }
               
                
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
            finally
            {
                Conn.Close();
                cmd.Dispose();
                rd.Dispose();
            }
        }
        private void LoadTable_Click(object sender, EventArgs e)
        {
            Load_Table(radTreeView1);
        }

        private void radTreeView1_DoubleClick(object sender, EventArgs e)
        {
            try
            {
                RadTreeNode node = radTreeView1.SelectedNode;
                if (node.Level == 0)
                {
                    if (txt_From.Text == "")
                        txt_From.Text += " FROM " + radTreeView1.SelectedNode.Text;
                    else
                    {
                        txt_From.Text += " INNER JOIN " + radTreeView1.SelectedNode.Text + " ON ";
                    }
                }
                else
                {
                    if (txt_Select.Text == "")
                    {
                        cb_Columns_select.Items.Clear();
                        txt_Select.Text += " SELECT TOP 1000 " + radTreeView1.SelectedNode.Text;
                        cb_Columns_select.Items.Add(radTreeView1.SelectedNode.Text);
                    }
                    else
                    {
                        txt_Select.Text += ", " + radTreeView1.SelectedNode.Text;
                        cb_Columns_select.Items.Add(radTreeView1.SelectedNode.Text);
                    }
                }

            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }
        private TreeNode FindRootNode(TreeNode treeNode)
        {
            while (treeNode.Parent != null)
            {
                treeNode = treeNode.Parent;
            }
            return treeNode;
        }
        private void run_select()
        {
            Conn = prs.Connect_DBScansia();
            //SqlCommand cmd = new SqlCommand();
            Conn.Open();
            DataSet ds = new DataSet();
            try
            {
                string_temp = txt_Select.Text + " " + txt_From.Text + " " + txt_Where.Text + " " + txt_groupby.Text;
                string_temp = string_temp.ToUpper();
                //MessageBox.Show(string_temp);
                if (string_temp.Contains("INSERT") || string_temp.Contains("UPDATE") || string_temp.Contains("DELETE") || string_temp.Contains("CREATE") || string_temp.Contains("DROP") || string_temp.Contains("ALTER"))
                {
                    MessageBox.Show("Xin lỗi, Câu lệnh của bạn đang chứa những từ khóa đặc biệt.");
                }
                else
                {
                    string_temp = txt_Select.Text + " " + txt_From.Text + " " + txt_Where.Text + " " + txt_groupby.Text;
                    ds = prs.dts(string_temp, Conn, ds);
                    dataGridView4.DataSource = ds.Tables[0];
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show("Kiểm tra lại cấu trúc câu",ex.Message);
                txt_Err.Text = ex.Message;
                
            }
            finally
            {
                Conn.Close();
                //cmd.Dispose();
            }
        }
        private void bt_runselect_Click(object sender, EventArgs e)
        {
            run_select();
        }
        private void bt_LoadData_Exc_Click(object sender, EventArgs e)
        {
            Load_Table(radTreeView2);
        }
        private void bt_Run_Exc_Click(object sender, EventArgs e)
        {
            Conn = prs.Connect_DBScansia();
            Conn.Open();
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = Conn;
            try
            {
                cmd.CommandText = richTextBox1.Text;
                DialogResult TL;
                TL = MessageBox.Show("BẠN CÓ MUỐN THỰC HIỆN LỆNH NÀY ?", "THÔNG BÁO", MessageBoxButtons.YesNo, MessageBoxIcon.Question);
                if (TL == DialogResult.Yes)
                {
                    if (lb_Area_Planning.Text == "administrator" &&lb_Useraccount.Text=="vuthaihuynh")
                    {
                        cmd.ExecuteNonQuery();
                        MessageBox.Show("Giao tác thực hiện thành công");
                    }
                    else
                    {
                        MessageBox.Show("Xin lỗi, Bạn không được phép thực hiện những cấu trúc lệnh này");
                    }
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
                richTextBox2.Text = ex.Message;
            }
            finally
            {
                Conn.Close();
                cmd.Dispose();
            }
        }
        private void cb_Columns_select_Leave(object sender, EventArgs e)
        {
            if (txt_Where.Text == "")
                txt_Where.Text = " WHERE " + cb_Columns_select.Text;
            else
            {
                txt_Where.Text += " "+cb_Logic_operator.Text + " " + cb_Columns_select.Text;
            }
        }
        #endregion

        private void bt_exportexcel_sql_Click(object sender, EventArgs e)
        {
            if (dataGridView4.Rows.Count > 1)
            {
               // DataTable dt = (DataTable)radGridView2.DataSource;
                prs.ExportExcel_gridview_Win(dataGridView4, "C:\\Huynh\\DataExport_Excel.xlsx", 5, 1);
            }
            else
                MessageBox.Show("Không có dữ liệu để xuất ra excel");
        }

        private void FrPlanning_KeyUp(object sender, KeyEventArgs e)
        {
            if (e.Control)
            {

                if (e.KeyCode.Equals(Keys.Q))
                {
                    if (Page_Main.SelectedPage == Page_QueryDesign)
                    {
                        if (lb_Area_Planning.Text == "administrator")
                            Page_NonQuery.Enabled = true;


                    }
                }
            }
        }
        #region //Theo Doi Xuong Xe
        private void Connect_excelQC()
        {
        }
        private void bt_LoadData_Click(object sender, EventArgs e)
        {
            Conn = prs.Connect();
            Conn.Open();
            DataSet ds = new DataSet();
            try
            {

                DataTable dt = new DataTable();
                //Process prs = new Process();
                //prs.Excel_into_DataGrid(radGridView2, txt_path.Text);
                dt = prs.ImportExcel_DataGrid(txt_path.Text, "Sheet1");
                radGrid_QCCua.DataSource = dt;
                DataTable dt1 = new DataTable();
                dt1 = prs.ImportExcel_DataGrid(txt_path.Text, "Sheet2");
                radGrid_KHCua.DataSource = dt1;
                //MessageBox.Show(dt1.Rows.Count.ToString());
                /*for (int i = 0; i < dt.Columns.Count; i++)
                {
                    radGridView14.Columns[i].Width = 100;
                    //MessageBox.Show(radGridView2.Columns[i].Name.ToString());
                }*/
                Datebegin = Date_Split(txt_Datebegin_XX.Text) + " 00:00";
                Dateend = Date_Split(txt_Dateend_XX.Text) + " 23:59";
                string_temp = @"select [DVD1],[DVD2],[DVD3],SUM(SLNhap) as SLNhap FROM [Planning].[dbo].[NhapGo] where Ngay>='" + Datebegin + "' and Ngay<='" + Dateend + "' group by DVD1,[DVD2],[DVD3] having sum(SLNhap)>0";

                
                ds = prs.dts(string_temp, Conn, ds);
                if (radGrid_QCNhap.Rows.Count > 0)
                    radGrid_QCNhap.Rows.Clear();
                for (i = 0; i < ds.Tables[0].Rows.Count; i++)
                {
                    string_temp = ds.Tables[0].Rows[i]["DVD1"].ToString() + ds.Tables[0].Rows[i]["DVD2"].ToString() + ds.Tables[0].Rows[i]["DVD3"].ToString();
                    radGrid_QCNhap.Rows.Add(string_temp, ds.Tables[0].Rows[i]["DVD1"].ToString(), ds.Tables[0].Rows[i]["DVD2"].ToString(), ds.Tables[0].Rows[i]["DVD3"].ToString(), ds.Tables[0].Rows[i]["SLNhap"].ToString(),"0");
                }

                cb_DataGrid_excelXX.Items.Clear();
                cb_DataGrid_excelXX.Items.Add("radGrid_KHCua");
                cb_DataGrid_excelXX.Items.Add("radGrid_QCCua");
                cb_DataGrid_excelXX.Items.Add("radGrid_QCNhap");
                cb_DataGrid_excelXX.Items.Add("radGrid_TheoDoi");
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
            finally
            {
                Conn.Close();
                ds.Clear();
            }
        }
        private void Add_QCTheoDoi()
        {
            try
            {
                double SLThanh = 0, TLDoi = 0, SLCan = 0,SLKH=0;
                string MaCT_KH,MaCT_QCCua,CDayCX,CRongCX,CDaiCX;
                if (radGrid_TheoDoi.Rows.Count > 0)
                    radGrid_TheoDoi.Rows.Clear();
                //int_temp = radGrid_QCNhap.Rows.Count;
                //k = radGrid_QCCua.Rows.Count;
                count = 0;
                if (radGrid_QCNhap.Rows.Count > 0)
                {
                    for (i = 0; i < radGrid_QCNhap.Rows.Count; i++)
                    {
                        ID = radGrid_QCNhap.Rows[i].Cells["ID"].Value.ToString().Trim();
                        //MessageBox.Show(ID);
                        SLNhap_d = double.Parse(radGrid_QCNhap.Rows[i].Cells["SLNhap"].Value.ToString());
                        
                       
                        for (j = 0; j < radGrid_QCCua.Rows.Count; j++) //Duyet bang chi tiet QC cua
                        {
                            //count = 0;
                            string_temp = radGrid_QCCua.Rows[j].Cells["ID"].Value.ToString().Trim();
                           // MessageBox.Show(ID+" "+string_temp);
                            if (ID == string_temp) //Neu ID trong QC Nhap cua xe == ID trong bang Cua
                            {
                                
                                SLThanh = double.Parse(radGrid_QCCua.Rows[j].Cells["SLThanh"].Value.ToString());
                                TLDoi = double.Parse(radGrid_QCCua.Rows[j].Cells["TLDoi"].Value.ToString());
                                TenSP1 = radGrid_QCCua.Rows[j].Cells["TenSP"].Value.ToString();
                                TenCT1 = radGrid_QCCua.Rows[j].Cells["TenChiTiet"].Value.ToString();
                                CDayCX = radGrid_QCCua.Rows[j].Cells["CDayCX"].Value.ToString();
                                CRongCX = radGrid_QCCua.Rows[j].Cells["CRongCX"].Value.ToString();
                                CDaiCX = radGrid_QCCua.Rows[j].Cells["CDaiCX"].Value.ToString();
                                MaCT_QCCua = radGrid_QCCua.Rows[j].Cells["MaChiTiet"].Value.ToString().Trim();
                                for (k = 0; k < radGrid_KHCua.Rows.Count; k++) //Duyet bang ke hoach, xem co hay ko
                                {
                                    MaCT_KH = radGrid_KHCua.Rows[k].Cells["MaChiTiet"].Value.ToString().Trim();
                                    if (MaCT_QCCua == MaCT_KH) //Neu Chi tiet trong bang cua == voi chi tiet trong ke hoach dang can
                                    {
                                        SLKH = double.Parse(radGrid_KHCua.Rows[k].Cells["SLKH"].Value.ToString());
                                        if (SLKH > 0) //Neu so luong KH dang can >0
                                        {
                                            temp_double = SLNhap_d * TLDoi * SLThanh - SLKH;
                                            if (temp_double > 0)
                                            {
                                                radGrid_TheoDoi.Rows.Add(ID, TenSP1, TenCT1, CDayCX, CRongCX, CDaiCX, SLKH);
                                                radGrid_KHCua.Rows[k].Cells["SLKH"].Value = "0";
                                                SLNhap_d = (int)temp_double / (TLDoi * SLThanh);
                                               // radGrid_QCNhap.Rows[
                                                break; //Da dap ung duoc ke hoach cua chi tiet nay
                                            }
                                            else
                                            {
                                                radGrid_TheoDoi.Rows.Add(ID, TenSP1, TenCT1, CDayCX, CRongCX, CDaiCX, (int)(SLNhap_d * TLDoi * SLThanh));
                                                SLNhap_d = 0;
                                                radGrid_KHCua.Rows[k].Cells["SLKH"].Value = ((int)(-temp_double)).ToString();
                                                //count = 1;
                                                break; //Chua dap ung duoc chi tiet cua ke hoach, nhung SLNhap da het, nen break luon, dong thoi break luon vong lap cua QC nay
                                            }
                                        }
                                        else //So luong KH <=0 thi ko can xet nua
                                            break;
                                    }
                                }

                                
                            }
                            if (SLNhap_d==0) //QC nhap khong con dap ung duoc cho bat cu quy cach nao nua
                                break;
                        }
                        radGrid_QCNhap.Rows[i].Cells["SLConLai"].Value = ((int)SLNhap_d).ToString();
                        /*if (count==0)
                            radGridView15.Rows.Add(ID, "", "", radGridView12.Rows[i].Cells["CDay"].Value.ToString(), radGridView12.Rows[i].Cells["CRong"].Value.ToString(), radGridView12.Rows[i].Cells["CDai"].Value.ToString(), "0", SLNhap_d);*/

                    }
                   
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }
        private void bt_DoiChieu_Click(object sender, EventArgs e)
        {
            Add_QCTheoDoi();
        }
        private void cb_MaSP_XX_SelectedIndexChanged(object sender, Telerik.WinControls.UI.Data.PositionChangedEventArgs e)
        {
            MaSP1 = prs.substring_right(cb_MaSP_XX.Text, child);
            cb_MaCT_XX.Items.Clear();
            cb_MaCT_XX.Text = "";
            for (i = dataGridView_LoadSP.Rows.Count - 2; i >= 0; i--)
            {
                if (MaSP1 == dataGridView_LoadSP.Rows[i].Cells[0].Value.ToString())
                {
                    cb_MaCT_XX.Items.Add(dataGridView_LoadSP.Rows[i].Cells[1].Value.ToString());
                }
            }
        }
        private void cb_MaCT_XX_SelectedIndexChanged(object sender, Telerik.WinControls.UI.Data.PositionChangedEventArgs e)
        {
            try
            {
                string_temp = cb_MaCT_XX.Text.Trim();
                for (i = dataGridView_LoadSP.Rows.Count - 2; i >= 0; i--)
                {
                    if (string_temp == dataGridView_LoadSP.Rows[i].Cells[1].Value.ToString())
                    {
                        lb_CDay.Text = dataGridView_LoadSP.Rows[i].Cells["ChieuDaySC"].Value.ToString();
                        lb_CRong.Text = dataGridView_LoadSP.Rows[i].Cells["ChieuRongSC"].Value.ToString();
                        lb_CDai.Text = dataGridView_LoadSP.Rows[i].Cells["ChieuDaiSC"].Value.ToString();
                        break;
                    }
                }
            }
            catch(Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }
        #endregion

        private void rd_Add_QCXe_Click(object sender, EventArgs e)
        {
            Conn = prs.Connect();
            Conn.Open();
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = Conn;
            cmd.CommandText = @"SELECT [QuyCach],[CDay],[CRong],[CDai] FROM [dbo].[QuyCach] where CDay-"+lb_CDay.Text+">=0 and CDay-"+lb_CDay.Text+"<=1.5 and CRong-"+lb_CRong.Text+">=0 and CRong-"+lb_CRong.Text+"<=1.5 and CDai>="+lb_CDai.Text ;
            //int g = (int)(17 / 3);
            //MessageBox.Show(cmd.CommandText);
            try
            {
                temp_double = double.Parse(lb_CDai.Text); 
                SqlDataReader rd = cmd.ExecuteReader();
                while (rd.Read())
                {
                    SoLuong_d=double.Parse(rd["CDai"].ToString());
                    k = (int)(SoLuong_d / temp_double);
                    SoKhoi_d = SoLuong_d - k * temp_double;
                    radGridView12.Rows.Add(rd["QuyCach"].ToString(), prs.substring_left(cb_MaSP_XX.Text, child), prs.substring_right(cb_MaCT_XX.Text, child), prs.substring_left(cb_MaCT_XX.Text, child), rd["CDay"].ToString(), rd["CRong"].ToString(), rd["CDai"].ToString(), k,0, SoKhoi_d);
                }
                rd.Close();
                cb_MaSP_XX.Focus();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
            finally
            {
                Conn.Close();
                cmd.Dispose();
            }
        }

        private void radGridView12_DoubleClick(object sender, EventArgs e)
        {
            if (radGridView12.Rows.Count > 0)
            {
                try
                {
                    //MessageBox.Show(radGridView1.Rows.Count.ToString());
                    int r = radGridView12.CurrentCell.RowIndex;
                    radGridView12.Rows.RemoveAt(r);

                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.Message);
                }
            }
        }

        private void bt_Exportexcel_QCXe_Click(object sender, EventArgs e)
        {
            if (radGridView12.Rows.Count > 1)
            {
                //DataTable dt = (DataTable)radGridView12.DataSource;
                //prs.ExportExcel(dt, "C:\\Huynh\\KhoChoRap_Planning.xlsx", 6, 1);
                prs.ExportExcel_grid(radGridView12, "C:\\Huynh\\KHCuaXe.xlsx", 2, 1);
            }
            else
                MessageBox.Show("Không có dữ liệu để xuất ra excel");
        }

        private void bt_Excel_XX_Click(object sender, EventArgs e)
        {


            if (cb_DataGrid_excelXX.Text == "radGrid_KHCua")
            {
                if (radGrid_KHCua.Rows.Count > 0)
                {
                    prs.ExportExcel_gridview_Telerik(radGrid_KHCua, "C:\\Huynh\\DataExport_Excel.xlsx", 5, 1);
                }
            }

            if (cb_DataGrid_excelXX.Text == "radGrid_QCCua")
            {
                if(radGrid_QCCua.Rows.Count>0)
                    prs.ExportExcel_gridview_Telerik(radGrid_QCCua, "C:\\Huynh\\DataExport_Excel.xlsx", 5, 1);
            }

            if (cb_DataGrid_excelXX.Text == "radGrid_QCNhap")
            {
                if(radGrid_QCNhap.Rows.Count>0)
                    prs.ExportExcel_gridview_Telerik(radGrid_QCNhap, "C:\\Huynh\\DataExport_Excel.xlsx", 5, 1);
            }

            if (cb_DataGrid_excelXX.Text == "radGrid_TheoDoi")
            {
                if(radGrid_TheoDoi.Rows.Count>0)
                    prs.ExportExcel_gridview_Telerik(radGrid_TheoDoi, "C:\\Huynh\\DataExport_Excel.xlsx", 5, 1);
            }
        }
        #region //Theo Doi Tong Hop
        private DataTable TheoDoiTonMB()
        {
            //KV1-KV2
            /*string_temp = @"select Load_CTSP_Mua.MaSP,Load_CTSP_Mua.TenSP,Load_CTSP_Mua.MaChiTiet,Load_CTSP_Mua.TenChiTiet,Load_CTSP_Mua.ChieuDaySC,Load_CTSP_Mua.ChieuRongSC,Load_CTSP_Mua.ChieuDaiSC,TonMB.TonKTG*ChiTiet.SLCT_QuyDoi as TonKTG,TonMB.SLTonMB*ChiTiet.SLCT_QuyDoi as SLTonMB,TonMB.TonKhoKV2*ChiTiet.SLCT_QuyDoi as TonKhoKV2
                            from (select ChiTiet_KhuVuc.MaCT,ChiTiet_KhuVuc.ID_link,ChiTiet_KhuVuc.SLCT_QuyDoi from dbo.ChiTiet_KhuVuc where ChiTiet_KhuVuc.KhuVuc='KV1SB') as ChiTiet left join
                            (select qry1.MaCT,qry1.TonKTG,qry2.SLTonMB,qry3.TonKhoKV2 from (select KTG_TK.MaCT,sum(dbo.KTG_TK.TonKho) as TonKTG from dbo.KTG_TK group by MaCT) as qry1 
                            left join (select KV1_KTG.MaCT,sum(KV1_KTG.SLXuat) as SLTonMB from dbo.KV1_KTG left join dbo.KV2_LXP on KV2_LXP.Serial_link=KV1_KTG.Serial where KV2_LXP.Serial_link is null group by KV1_KTG.MaCT) as qry2
                            on qry1.MaCT=qry2.MaCT left join (select dbo.KV2_TK.SLTon as TonKhoKV2,dbo.KV2_TK.MaCT from dbo.KV2_TK) as qry3 
                            on qry3.MaCT=qry1.MaCT) as TonMB on ChiTiet.ID_link=TonMB.MaCT inner join Load_CTSP_Mua on Load_CTSP_Mua.MaChiTiet=ChiTiet.MaCT";
            //KV3
            string_temp = @"select Load_CTSP_Mua.TenSP,Load_CTSP_Mua.MaChiTiet,Load_CTSP_Mua.TenChiTiet,qry.VeKTam,TonMB.SLTDoi*qry.VeQuydoi as TonMBLapRap,TonMB.TonKhoKTam*VeQuydoi as TonKTam
                            from (select KTam_TK.MaCT,KTam_TK.TonKho as TonKhoKTam,qryMBKV3.SLTDoi from (select KH_Theodoi.MaCT,sum(KH_Theodoi.SLTheoDoi) as SLTDoi
                            from dbo.KH_Theodoi where KH_Theodoi.SLTheoDoi>0 and KH_Theodoi.ID_KeHoach like '%KV2XLR%' group by MaCT) as qryMBKV3 full outer join dbo.KTam_TK on qryMBKV3.MaCT=KTam_TK.MaCT) as TonMB
                            inner join (select qrya.MaCT as ChiTietSB,qryb.MaCT as ChiTietKV2,qrya.SLCT_QuyDoi as SLQuyDoiSB,qryb.ID_link as VeKTam,qryb.SLCT_QuyDoi as VeQuydoi from (select ChiTiet_KhuVuc.MaCT,ChiTiet_KhuVuc.ID_link,ChiTiet_KhuVuc.SLCT_QuyDoi from ChiTiet_KhuVuc where ChiTiet_KhuVuc.KhuVuc='KV1SB')
                            as qrya inner join (select ChiTiet_KhuVuc.MaCT,ChiTiet_KhuVuc.ID_link,ChiTiet_KhuVuc.SLCT_QuyDoi from ChiTiet_KhuVuc where ChiTiet_KhuVuc.KhuVuc='KV2XP') as qryb
                            on qrya.ID_link=qryb.MaCT) as qry on qry.VeKTam=TonMB.MaCT inner join dbo.Load_CTSP_Mua on Load_CTSP_Mua.MaChiTiet=qry.ChiTietSB";
            //KV4-KV5
            string_temp = @"select Load_CTSP_Mua.TenSP,qry1.ChiTietSB,Load_CTSP_Mua.TenChiTiet,Load_CTSP_Mua.SoLuongCT,dbo.KTam_TK.MaCT,KTam_TK.TonKho*qry1.VeQuydoi as SLTonKTam,TonMB.TonMBKV4*VeQuydoi as TonMBKV4,TonMB.TonKhoTP*VeQuydoi as TonKTP from dbo.KTam_TK inner join (select qrya.MaCT as ChiTietSB,qryb.MaCT as ChiTietKV2,qrya.SLCT_QuyDoi as SLQuyDoiSB,qryb.ID_link as VeKTam,qryb.SLCT_QuyDoi as VeQuydoi 
                            from (select ChiTiet_KhuVuc.MaCT,ChiTiet_KhuVuc.ID_link,ChiTiet_KhuVuc.SLCT_QuyDoi from ChiTiet_KhuVuc where ChiTiet_KhuVuc.KhuVuc='KV1SB')
                            as qrya inner join (select ChiTiet_KhuVuc.MaCT,ChiTiet_KhuVuc.ID_link,ChiTiet_KhuVuc.SLCT_QuyDoi from ChiTiet_KhuVuc where ChiTiet_KhuVuc.KhuVuc='KV2XP') as qryb
                            on qrya.ID_link=qryb.MaCT) as qry1 on KTam_TK.MaCT=qry1.VeKTam left join (select ChiTiet.MaSP,qryKTam.MaCT,(qryKTam.SLXuat-isnull(qryKTP.NhapKTP*ChiTiet.SLCT_QuyDoi,0)) as TonMBKV4,KhoTP_TK.TonKho*ChiTiet.SLCT_QuyDoi as TonKhoTP 
                            from (select ChiTiet_KhuVuc.MaSP,ChiTiet_KhuVuc.MaCT,ChiTiet_KhuVuc.SLCT_QuyDoi from dbo.ChiTiet_KhuVuc where ChiTiet_KhuVuc.KhuVuc='KV3') as ChiTiet 
                            left join (select KV4_KTam.MaCT,sum(KV4_KTam.SLXuat)as SLXuat from dbo.KV4_KTam group by MaCT) as qryKTam on qryKTam.MaCT=ChiTiet.MaCT 
                            left join (select sum(KhoTP_NK.SLNhap) as NhapKTP, KhoTP_NK.MaSP from dbo.KhoTP_NK where LyDo='TPNM' group by MaSP) as qryKTP on ChiTiet.MaSP=qryKTP.MaSP 
                            left join dbo.KhoTP_TK on KhoTP_TK.MaSP=ChiTiet.MaSP) as TonMB on qry1.VeKTam=TonMB.MaCT inner join dbo.Load_CTSP_Mua on Load_CTSP_Mua.MaChiTiet=qry1.ChiTietSB";*/
            DataSet dt = new DataSet();
            Conn = prs.Connect_DBScansia();
            Conn.Open();
           
            if (cb_KhuVuc_Begin.Text == "KV1")
            {
                if (cb_KhuVuc_End.Text == "KV2")
                    string_temp = @"SELECT [MaSP],[TenSP],[MaChiTiet],[TenChiTiet],[SoLuongCT],[TonKTG]/[SoLuongCT] as TonKTG,[SLHCKV2]/[SoLuongCT] as SLHCKV2,(TonMB_KV12.TonMBKV2-SLHCKV2)/SoLuongCT as TonMBKV2,[TonKhoKV2]/[SoLuongCT] as TonKhoKV2
                                    FROM [dbo].[TonMB_KV12]";
                if (cb_KhuVuc_End.Text == "KV3")
                    string_temp = @"select dbo.TonMB_KV12.TenSP,dbo.TonMB_KV12.MaChiTiet,dbo.TonMB_KV12.TenChiTiet,dbo.TonMB_KV12.SoLuongCT,dbo.TonMB_KV12.TonKTG/TonMB_KV12.SoLuongCT as TonKTG,dbo.TonMB_KV12.SLHCKV2/TonMB_KV12.SoLuongCT as SLHCKV2,(TonMB_KV12.TonMBKV2-TonMB_KV12.SLHCKV2)/TonMB_KV12.SoLuongCT as TonMBKV2,dbo.TonMB_KV12.TonKhoKV2/TonMB_KV12.SoLuongCT as TonKhoKV2,dbo.TonMB_KV3.SLHCKV3/TonMB_KV12.SoLuongCT as SLHCKV3,(dbo.TonMB_KV3.TonMBLapRap-dbo.TonMB_KV3.SLHCKV3)/TonMB_KV12.SoLuongCT as TonMBLR, dbo.TonMB_KV3.TonKTam/TonMB_KV12.SoLuongCT as TonKTam
                                    from dbo.TonMB_KV12 left join dbo.TonMB_KV3 on TonMB_KV12.MaChiTiet=TonMB_KV3.MaChiTiet";
                if (cb_KhuVuc_End.Text == "KV4" || cb_KhuVuc_End.Text == "KV5")
                    string_temp = @"select dbo.TonMB_KV12.TenSP,dbo.TonMB_KV12.MaChiTiet,dbo.TonMB_KV12.TenChiTiet,dbo.TonMB_KV12.SoLuongCT,dbo.TonMB_KV12.TonKTG/TonMB_KV12.SoLuongCT as TonKTG,dbo.TonMB_KV12.SLHCKV2/TonMB_KV12.SoLuongCT as SLHCKV2,(dbo.TonMB_KV12.TonMBKV2-dbo.TonMB_KV12.SLHCKV2)/TonMB_KV12.SoLuongCT as TonMBKV2,dbo.TonMB_KV12.TonKhoKV2/TonMB_KV12.SoLuongCT as TonKhoKV2,dbo.TonMB_KV3.SLHCKV3/TonMB_KV12.SoLuongCT as SLHCKV3,(dbo.TonMB_KV3.TonMBLapRap-dbo.TonMB_KV3.SLHCKV3)/TonMB_KV12.SoLuongCT as TonMBLR, dbo.TonMB_KV3.TonKTam/TonMB_KV12.SoLuongCT as TonKTam,dbo.TonMB_KV45.SLHCKV4/TonMB_KV12.SoLuongCT as SLHCKV4,(TonMB_KV45.[TonMBKV4]-dbo.TonMB_KV45.SLHCKV4)/TonMB_KV12.SoLuongCT as TonMBKV4,TonMB_KV45.[TonKTP]/TonMB_KV12.SoLuongCT as TonKTP
                                from dbo.TonMB_KV12 left join dbo.TonMB_KV3 on TonMB_KV12.MaChiTiet=TonMB_KV3.MaChiTiet left join dbo.TonMB_KV45 on TonMB_KV45.ChiTietSB=TonMB_KV12.MaChiTiet";
            }
            if (cb_KhuVuc_Begin.Text == "KV2")
            {
                if (cb_KhuVuc_End.Text == "KV3")
                    string_temp = @"SELECT [TenSP],[MaChiTiet],[SoLuongCT],[TenChiTiet],[VeKTam],[SLHCKV3]/SoLuongCT as SLHCKV3,(TonMBLapRap-[SLHCKV3])/SoLuongCT as TonMBLR,[TonKTam]/SoLuongCT as TonKTam
                                    FROM [dbo].[TonMB_KV3]";
                if (cb_KhuVuc_End.Text == "KV4" || cb_KhuVuc_End.Text == "KV5")
                    string_temp = @"select TonMB_KV3.TenSP,TonMB_KV3.MaChiTiet,dbo.TonMB_KV3.TenChiTiet,TonMB_KV3.SoLuongCT,(TonMB_KV3.SLHCKV3/TonMB_KV3.SoLuongCT) as SLHCKV3,(TonMB_KV3.TonMBLapRap-TonMB_KV3.SLHCKV3)/TonMB_KV3.SoLuongCT as TonMBLR,TonMB_KV3.TonKTam/TonMB_KV3.SoLuongCT as TonKTam,dbo.TonMB_KV45.SLHCKV4/TonMB_KV3.SoLuongCT as SLHCKV4,(TonMB_KV45.TonMBKV4-dbo.TonMB_KV45.SLHCKV4)/TonMB_KV3.SoLuongCT as TonMBKV4,dbo.TonMB_KV45.TonKTP/TonMB_KV3.SoLuongCT as TonKTP
                                from dbo.TonMB_KV3 inner join dbo.TonMB_KV45 on TonMB_KV3.MaChiTiet=TonMB_KV45.ChiTietSB";
            }
            if (cb_KhuVuc_Begin.Text == "KV3")
            {
                if (cb_KhuVuc_End.Text == "KV4" || cb_KhuVuc_End.Text == "KV5")
                    string_temp = @"SELECT [TenSP],[ChiTietSB],[TenChiTiet],[SoLuongCT],[SLTonKTam]/[SoLuongCT] as SLTonKTam ,[SLHCKV4]/[SoLuongCT] as SLHCKV4,([TonMBKV4]-[SLHCKV4])/[SoLuongCT] as TonMBKV4,[TonKTP]/[SoLuongCT] as TonKTP
                                FROM [dbo].[TonMB_KV45]";
            }
            dt = prs.dts(string_temp, Conn, dt);
            Conn.Close();
            return dt.Tables[0];
          

           
        }
        private void radButton1_Click(object sender, EventArgs e)
        {
            DataTable dt = new DataTable();
            try
            {
                dt = TheoDoiTonMB();
                
                dataGridView5.DataSource = dt;
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
            finally
            {
            }
        }
        #endregion

        private void bt_print_TonMB_Click(object sender, EventArgs e)
        {
            DataTable ds = TheoDoiTonMB();
            Fr_Report FrR = new Fr_Report();
            Conn = prs.Connect_DBScansia();
            SqlCommand cmd = new SqlCommand();
            Conn.Open();
            cmd.CommandText = "select convert(varchar(10),GETDATE(),103)+' '+CONVERT(varchar(10),getdate(),108)";
            cmd.Connection=Conn;
            try
            {

                string_temp = cmd.ExecuteScalar().ToString();
                FrR.report_TonMB(ds, string_temp);
                // FrR.report_KV1NKTG(ds, Ngay1);
                FrR.Show();
                ds.Clear();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
            finally
            {
                Conn.Close();
                cmd.Dispose();
            }
        }
        #region //Ton MB KV2
        private void bt_Search_TonMBKV2_Click(object sender, EventArgs e)
        {
            
            Fr_Report FrR = new Fr_Report();
            DataSet ds = new DataSet();
            Conn = prs.Connect_DBScansia();
            SqlCommand cmd = new SqlCommand();
            Conn.Open();
            cmd.CommandText = "select convert(varchar(10),GETDATE(),103)+' '+CONVERT(varchar(10),getdate(),108)";
            cmd.Connection = Conn;
            try
            {
                if (chk_DinhVi.Checked == true)
                {
                    string_temp = @"select * from dbo.TonDinhVi_new";
                    ds = prs.dts(string_temp, Conn, ds);
                    string_temp = cmd.ExecuteScalar().ToString();
                    FrR.report_TonMB_KV2(ds.Tables[0], string_temp);
                }
                if (chk_LapRap.Checked == true)
                {
                    string_temp = @"select * from dbo.TonLapRap";
                    ds = prs.dts(string_temp, Conn, ds);
                    string_temp = cmd.ExecuteScalar().ToString();
                    FrR.report_TonMB_LapRap(ds.Tables[0], string_temp);
                }
                
                
               
                // FrR.report_KV1NKTG(ds, Ngay1);
                FrR.Show();
                ds.Clear();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
            finally
            {
                Conn.Close();
                cmd.Dispose();
            }
        }
        private void bt_print_TonMBNhung_Click(object sender, EventArgs e)
        {
            Fr_Report FrR = new Fr_Report();
            DataSet ds = new DataSet();
            Conn = prs.Connect_DBScansia();
            SqlCommand cmd = new SqlCommand();
            Conn.Open();
            cmd.CommandText = "select convert(varchar(10),GETDATE(),103)+' '+CONVERT(varchar(10),getdate(),108)";
            cmd.Connection = Conn;
            try
            {
                if (chk_LapRapKV3.Checked == true)
                {
                    string_temp = @"select * from dbo.TonLapRap";
                    ds = prs.dts(string_temp, Conn, ds);
                    string_temp = cmd.ExecuteScalar().ToString();
                    FrR.report_TonMB_LapRap(ds.Tables[0], string_temp);
                }
                if (chk_SauNhung.Checked == true)
                {
                    string_temp = @"SELECT [TenSP],[MaChiTiet],[TenChiTiet],[SoLuongCT],[TonKTam],[SLHCKV4],SLNhap,SLXuat,[TonMBKV4],[DBTon]
                                    FROM [TonSauNhung]";
                    ds = prs.dts(string_temp, Conn, ds);
                    string_temp = cmd.ExecuteScalar().ToString();
                    FrR.report_TonMB_SauNhung(ds.Tables[0], string_temp);
                }
                if (chk_TonKV34.Checked == true)
                {
                    string_temp = @"SELECT  [TenSP],[TenChiTiet],[MaChiTiet],[SoLuongCT],[TonMBLR],[TonKTam],[TonMBKV4],[TonKV34] as SLTon,[TonKV34]/SoLuongCT as DBTon
                        FROM [dbo].[TonMBKV3]";
                    ds = prs.dts(string_temp, Conn, ds);
                    string_temp = cmd.ExecuteScalar().ToString();
                    FrR.report_TonMB_KV34(ds.Tables[0], string_temp);
                }


                // FrR.report_KV1NKTG(ds, Ngay1);
                FrR.Show();
                ds.Clear();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
            finally
            {
                Conn.Close();
                cmd.Dispose();
            }
        }
        #endregion

       



































    }
}
